change font colour if cell contains function - programmatically

[fitful_thought]

How do I get the font color to be black if cells(3,3) contains "=today()"
but to be blue if cells(3,3) contains (for example) "30 Oct 2004"?

 

[Rob van Gelder]

Here's the code way:

Sub test()
With Cells(3, 3)
.Interior.Color = IIf(.HasFormula, vbBlue, vbBlack)
End With
End Sub


There is also a Named Range method, which is kind of dodgy and crashes XL97
users under specific conditions - on my website "IsFormula"

 

[Jake Marx]

Hi fitful_thought,

How do I get the font color to be black if cells(3,3) contains
"=today()" but to be blue if cells(3,3) contains (for example) "30
Oct 2004"?

Something like this may do what you need:

Sub ColorCells()
On Error Resume Next
With Sheet1.UsedRange
.SpecialCells(xlCellTypeFormulas).Font.Color = vbBlack
.SpecialCells(xlCellTypeConstants).Font.Color = vbBlue
End With
On Error GoTo 0
End Sub

This changes font colors for the entire usedrange of a worksheet - you could
use the HasFormula property of the Range object to determine if the cell
contains a formula or not:

Sub ColorCells2()
With Sheet1.Range("A3")
If .HasFormula Then
.Font.Color = vbBlack
Else
.Font.Color = vbBlue
End If
End With
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[fitful_thought]

Thanks Rob,
That solution works well.
I've never used Iif before.