Date difference with loop ?

[Zaharoff]

Hi,
I have a table to organise document receiption. I have 2 column : the first it the expected date, the second it the reveived date.
I made a macro to highlight in red the first column when dates are late if nothing received.
Now i want to highlight in an other color the 2nd and the 1st column when the document reveived is late. Means i want the difference between these 2 dates, and then if the difference is >0 or <0 (depends of the sign) i want to highlight the 2 cells.

I have this for my first column, that works.

If Not Range("G" & k) = "" Then
Range("G" & k).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=G" & k & "<TODAY()"

Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=IF(AND(G" & k & "<=5+TODAY()),(G" & k & ">TODAY()))"
Selection.FormatConditions(2).Interior.ColorIndex = 36
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=G" & k & "=TODAY()"
Selection.FormatConditions(3).Interior.ColorIndex = 20
End If

Next k

I tried something like this for the 2nd column, but that does not work.


If Not Range("H" & k) = "" Then
Range("H" & k).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=H" & k & "<G"

Selection.FormatConditions(1).Interior.ColorIndex = 36
End If
Next k


Thanks for your help

 

[riaz]

Why not just use plain conditional formatting?