Conditional Formatting

  • Thread starter Thread starter Sheryl Lang
  • Start date Start date
S

Sheryl Lang

Is there a way to use conditional formatting to highlight
ANY cell with ANY formula in it??

I often inherit large spreadsheets without any idea where
the values are vs where the formulas are. It would be
really handy if I could find a way to identify which cells
the formulas are in easily.

Thanks in advance.

Sheryl
 
One quick way, select the range with data, press F5,
click special, select formulas, click OK. Click the paint bucket button on
the menu
and select a colour and click OK
 
Sheryl,

You could use a pair of macros.

This one will turn all formulas red:

Sub TurnFormulasRed()
With Cells.SpecialCells(xlCellTypeFormulas, 23)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
.FormatConditions(1).Interior.ColorIndex = 3
End With
End Sub

And this one will return them to normal:

Sub TurnFormulasNormal()
Cells.SpecialCells(xlCellTypeFormulas, 23).FormatConditions.Delete
End Sub

Of course, this will destroy any conditional formatting you already
have on cells with formulas.

HTH,
Bernie
MS Excel MVP
 
You could try 'tracing features' from the Auditing toolbar
and/or use the Ctrl and ~ methods to display what you may
need to know.
HTH
Gerry
 
Back
Top