Conditional Formating for Formula Cells

A

Andrew Heath

Hi to all fantastic Excel champions,

How do I get conditional formatting to change the format of any cell that
contains a formula?


Kind regards
 
D

David McRitchie

Hi Andrew,

You can use a user defined function (see my formula.htm page):
But it must be in the same workbook not your personal.xls to work
in Conditional Formatting, though there is no harm in having it in both places.

Function HasFormula(cell)
HasFormula = cell.HasFormula
End Function

=hasformula(a1)

use the above in your conditional formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm

If you need help to install a User Defined Function or a Macro see
http://www.mvps.org/dmcritchie/excel/getstarted.htm

alternative:
You can temporarily see which cells have formulas with Ctrl+A, Edit, GoTo,
[Special], Formulas, (check all: Numbers, Text, Logicals, Errors)
You could then use Format,cells, pattern to color the cells.
 
B

Bernd Plumhoff

Hello Andrew,

define the name HasFormula with the value
=GET.CELL(48,INDIRECT("RC",))

(go to Insert/Name/Define... and enter HasFormula into field Names in
workbook and
=GET.CELL(48,INDIRECT("RC",))
into field Refers to)

then select for example your whole workbook (left mouse-click on upper left
cell of sheet), go to Format/Conditional Formatting, select Formula Is for
Condition 1 and enter
=HasFormula
into next field, push button Format and select Pattern and the green color,
for example)

All cells with formulas will appear with a green background now.

Other interesting parameters for the get.cell() function you might find at
http://www.sulprobil.com/html/get_cell.html

HTH,
Bernd
 
Top