Highlight Excel Formulas

Way back in 2018 I wrote a post about using a conditional format to highlight formulas. Since then Dynamic Arrays were released and I thought I would create a macro to apply the conditional format easily.

The link to the old article is below.

In the image below there are only three formulas. Dynamic array formulas are always in the top left of the range.  

The macro below will apply a format to highlight formula cells with a light grey fill in the selected region.

Remember macros clear the Undo list. So you can’t undo anything after you run a macro.

Sub Identify_Formulas()
' Identify_Formulas Macro
' Applies a conditional format to highlight
' all formulas with light grey fill colour
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=ISFORMULA(" & Replace(Selection(1, 1).Address, "$", "") & ")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).Interior.Color = 13882323 'Light grey
End Sub

Select the range and run the macro and the formula cells will be identified.

These days dynamic arrays reduce the number of formulas, but it is still useful to see where they are.