Changing font color depending on cell contents

Martin
Here's a slightly different approach that I use.
I have the following 2 macros in my Personal workbook and attached to two buttons. The first macro removes all cell interior colors and then makes the interior color of all cells with formulas blue. The second macro removes all cell interior colors.
(Note : the cell highlighting is not automatic - the macros have to be run to update the highlighting.)

Sub IdentifyFormulae_Add()
On Error GoTo e:
With Cells
.Interior.ColorIndex = 0
.SpecialCells(xlFormulas).Interior.ColorIndex = 20
End With
Exit Sub
e: MsgBox "There are no cells with formulas"
End Sub

Sub IdentifyFormulae_Remove()
Cells.Interior.ColorIndex = 0
End Sub

Try this, you might want to change "I" to whatever cell you
want to search in....

Private Sub CommandButton1_Click()
Dim LastRow As Variant
Dim x As Variant

LastRow = Range("A65536").End(xlUp).Row

For x = 2 To LastRow
If Range("I" & x).Formula = True Then
Range("I" & x).Select
With Selection.Interior
.ColorIndex = 0
End With

Else

Range("I" & x).Select
With Selection.Interior
.ColorIndex = 1
End With

End If
'MsgBox "TEST"
Next x
End Sub

here's a non-VBA solution-

select cell A1
from menu bar select Insert>Name>Define
for 'Names in workbook' enter CellType
in 'Refers to' enter =get.cell(48,a1)
click ok

highlight the range to 'Conditional Format'
in the Conditional Format dialog box, 'Formula is' to highlight formulas is =CellType, and to highlight entered values is =not(CellType)

(Get.Cell is a holdover from the macro language before VBA)

HTH

Mark

Oops:

to keep blank cell from being highlighted, change the 'is formula' for entered values to
=and(not(CellType),not(isblank(xx)))
substituting the active cell address for the the range you are trying to conditionally format (i.e. A1)
sorry I didn't think it through better the first time.

Mark