Change row colour dependant on criteria

[AyKay86]

Hi guys & girls,

I need to get a row to highlight if one cell has a difference compared to another. The coding below works for the first cell, but others in the sequence do not change. I know the Range("A5:Z5") is wrong, but the loop is not working either, it's running the If statement even if the date in R7 is today.

For Each c In Range("R5:R10")
If c.Text < "Today()" Then
Range("A5:Z5").Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If
Next c

Any and all help appreciated.

Thanks

 

[xld]

    For Each c In Range("R5:R10")
        If c.Value < Date Then
            With c.Interior
                .ColorIndex = 3
                .Pattern = xlSolid
            End With
        End If
    Next c

 

[AyKay86]

That works, thanks very much. I've now been asked whether I can get it to look at column A for each row rather than the date. Instead of saying:

For Each c In Range("R5:R10")
If c.Value < Date Then
With c.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If
Next c

I need it say something like:

For Each c In Range("R5:R10")
If c.Value < Range("A5") Then
With c.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If
Next c

I know the red line won't work because it will compare everything with A5, but I was wondering if there is a way to get it to look at column A for each row?

Thanks

 

[xld]

    For Each c In Range("R5:R10")
        If c.Value < Cells(c.Row, "A").Value Then
            With c.Interior
                .ColorIndex = 3
                .Pattern = xlSolid
            End With
        End If
    Next c

 

[AyKay86]

That's briliant. Thank you very much.