Highlight Cells which Contain Formulas

Add to Favorites

This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight all of the cells with formulas within the active worksheet. The second macro listed will highlight all of the cells which contain a formula which are also within a predefined range of cells; to change this range simply change the cell references in this line of code For Each Rng In Range("A1:B25").

These macros, when run, will remove any previous color from the worksheet. So, if you have a very colorful worksheet and want to keep it that way, do not use this macro.

This macro is best used for when you have a large number of formulas and you're not sure if you know where they all are or if you inherit or receive worksheets from other people and you want to quickly locate all formulas.

To change the color of the highlight for the first macro, change the number in this line of code If r.HasFormula Then r.Interior.ColorIndex = 6 'yellow.

To change the color of the highlight for the second macro, change the number in this line of code Rng.Interior.ColorIndex = 3 'red.

Where to install the macro:  Module

Highlight Cells with Formulas - Works on entire active worksheet

Sub Highlight_Formulas()

'Highlights all cells with formulas on the active sheet
'Will remove color from cells without formulas

Dim r As Range

With ActiveSheet.UsedRange
.Interior.ColorIndex = xlNone
For Each r In .Cells
If r.HasFormula Then r.Interior.ColorIndex = 6  'yellow
Next
End With
End Sub

Highlight Cells with Formulas - Works on Predefined Range of Cells

Sub Highlight_ Formulas_Range()

'Highlights all cells with formulas within a selected range
'Will remove color from cells without formulas

Dim Rng As Range

For Each Rng In Range("A1:B25")  'Range to highlight cells

If Rng.HasFormula Then
Rng.Interior.ColorIndex = 3 'red
Else
Rng.Interior.ColorIndex = 0 'blank
End If

Next Rng

End Sub





Similar Content on TeachExcel
Highlight Cells with Text or Formulas (non-empty cells)
Macro: This macro will highlight all cells in excel which are not empty. This means that if a cel...
Count the Number of Cells that Contain Specific Text in Excel
Tutorial: How to count the number of cells that contain specific text within a spreadsheet in Excel....
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
Macro: Determine if a cell in Excel contains a formula or function with this UDF (user defined fu...
Reverse Cell Contents (Mirror)
Macro: This macro will completely reverse the contents of any cell. This means that if you h...
Highlight Rows that Meet a Certain Condition in Excel
Tutorial: In this tutorial I am going to cover how to highlight rows that meet a certain condition. ...
Highlight Duplicate Values in Excel
Tutorial: How to highlight duplicate values in a list. Also, how to arrange those values next to ea...



How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  8. Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

  9. You are now ready to run the macro.


Similar Content
Highlight Cells with Text or Formulas (non-empty cells)
Macro: This macro will highlight all cells in excel which are not empty. This means that if a cel...
Count the Number of Cells that Contain Specific Text in Excel
Tutorial: How to count the number of cells that contain specific text within a spreadsheet in Excel....
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
Macro: Determine if a cell in Excel contains a formula or function with this UDF (user defined fu...
Excel Forum