Login

  • Aug 12

Highlight Excel Formulas

  • Neale Blackwood
  • Macros
  • 0 comments

In the past I have written about using a conditional format to highlight formulas. I thought I would create a macro to apply the conditional format easily.

Sign up to hear about free Excel training.

I won't share your email with anyone.

  • 1 min read

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.

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.

0 comments

Joinor login to leave a comment