Dim NumRows As Long
Dim i As Long

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


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Cumberland" wrote
in message ...

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

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

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

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

I have tried a couple of ways. Here are the ways I've tried so far. The
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.


--
Cumberland
------------------------------------------------------------------------
Cumberland's Profile:
http://www.excelforum.com/member.php...o&userid=33445
View this thread: http://www.excelforum.com/showthread...hreadid=553980