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.
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
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