Conditional Formatting in For:Next loop - won't work!!!

[Cumberland]

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

 

[Bob Phillips]

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]

Thanks Bob, that's great - it works fine now.

Although I have to ask - why does your method work over my secon
method? Surely it's just a different way of doing the same thing?

Thanks again

 

[Bob Phillips]

I must admit I didn't try your second first time round, but I just have, and
it has the same problem with the Str function. If you read the help on Str,
it says ... When a number is converted to a string, a leading space is
always reserved for its sign..., so it embeds a space which causes a
problem. It is just not necessary anyway, but if you do want to coerce a
number, use CStr. Also, you setup a variable called Formu, but used another
called Formu1, it would be empty. Use Option Explicit.

--
HTH

Bob Phillips

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