Conditional Formatting Based on Cell Content

[Lee]

How do I create conditional formatting based on cell content rather than cell
value? I'm trying to highlight cells with different colors for cells that
contain constants vs. formulas.

 

[akphidelt]

Not sure if this helps because it doesn't do it in an ongoing basis, but if
you highlight all the cells and go to

Edit-->GoTo (Or just press Ctrl + G)
Then click on Special
Then click the box that says formulas and then ok

Now all the cells with formulas in them are hilighted and you can change all
the cells properties from there.

As far as conditional formatting goes, im not sure

 

[Lee]

I'm trying to set up a spreadsheet for another user so that it automatically
highlights the cell for them when a formula is overwritten with a constant,
so I was really trying to avoid having the user manually change the format.
Is it possible to incorporate your suggestion into a macro in combination
with the conditional formatting feature?

 

[Ken Johnson]

I'm trying to set up a spreadsheet for another user so that it automatically
highlights the cell for them when a formula is overwritten with a constant,
so I was really trying to avoid having the user manually change the format.
Is it possible to incorporate your suggestion into a macro in combination
with the conditional formatting feature?

Hi Lee,

Excel's worksheet functions do not include a function to test for the
presence of a formula. However, VBA has such a method called
HasFormula. So, if you write a User Defined Function (UDF) using the
HasFormula method you can then use it in the conditional formatting
Formula Is box...

Example UDF...

Public Function NowText(Range As Range) As Boolean
If Not Range.HasFormula Then NowText = True
End Function

Then, say you are wanting to test A1 for formula, then in the
conditional Formatting Formula Is box use =NowText(A1) and set the
format.
If A1 has a formula, NowText(A1) = False and the conditional
formatting is NOT applied. If A1s formula has been overwritten then
NowText(A1) = True and the conditional formatting is applied.

The UDF can either be pasted into your Personal Macro Workbook or a
standard code module.

Ken Johnson