Conditional Formatting

[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

 

[Peo Sjoblom]

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

 

[Bernie Deitrick]

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

 

[GerryK]

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