I want to apply conditional formatting across a range of rows accordin
to a date in column D using a For:Next loop. The problem I have i
applying the formula to a given row number in the conditional format.

The conditional format basically changes the colour of a given cell i
column A to red if TODAY() is greater than the date in th
corresponding cell in column D.

For example, for Row 3, the "Formula Is" entry in the conditiona
format dialog is "=$D3<TODAY()".

The problem I have is: how do I apply this formula to the required ro
number in the for:next loop?

I have tried a couple of ways. Here are the ways I've tried so far. Th
first row to start at is 3, hence the "i + 2"):

FIRST:

numrows = 77
For i = 1 To numrows
Range("A" & i + 2).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$D
& Str(i+2) + "<TODAY()"
Selection.FormatConditions(1).Font.ColorIndex = 2
Selection.FormatConditions(1).Interior.ColorIndex = 3
Next i

SECOND:

Dim Formu As String
For i = 1 To numrows
Formu = "=$D" & Str(i + 2) & "<TODAY()"
Range("A" & i + 2).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression
Formula1:=Formu1
Selection.FormatConditions(1).Font.ColorIndex = 2
Selection.FormatConditions(1).Interior.ColorIndex = 3
Next i

Neither of these methods worked - please tell me how I can solve this
It's driving me bonkers!!!!

Thanks

--
Cumberlan
-----------------------------------------------------------------------
Cumberland's Profile: http://www.excelforum.com/member.php...fo&userid=3344
View this thread: http://www.excelforum.com/showthread.php?threadid=55398