VBA to look at a date in A1 and then change font color of any Table1 dates that are < A1

[jase71ds]

Hoping someone will help me with this...

A1 = a user defined date (mm/dd/yy)
Table1 = a defined table range in which the values are either blank, or are dates (mm/dd/yy)

I need VBA to consider the date that is A1, and then look through Table1 and change the font color to red, of any cell in Table1 that contains a date that is less than the date in A1.

In other words: Within Table1, change a cell's font color to red if the date in that cell is < the date in A1.
(Note: A1 and Table1 are on the same worksheet page)

Normally I would use conditional formatting - but for reasons too long to discuss, I need to avoid CF.

Thanks for any help. As always, it is greatly appreciated.

Jase.

 

[rpaulson]

try this.

Sub Format_it()


For Each cell In Range("Table1")
If cell < [A1] And IsDate(cell) = True Then


With cell.Font
        .Color = -16776961
        .TintAndShade = 0
End With
    
Else
With cell.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
End With

End If

Next cell

End Sub

hth,

Ross

 

[jase71ds]

Ah! Thank you, thank you, thank you!!
Your code taught me what I was doing incorrectly!