One way:

Public Sub test_date2()
Dim rCell As Range
With ThisWorkbook.Sheets("sheet1").Range("D1:D" & _
Range("D" & Rows.Count).End(xlUp).Row)
.Interior.ColorIndex = xlColorIndexAutomatic
For Each rCell In .Cells
With rCell
If .Value < "Complete" Then
If .Offset(0, -1).Value 2 Then
With .Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If
End If
End With
Next rCell
End With
End Sub



In article om,
"kevcar40" wrote:

hi
i have the following code that checks two date (A1 and B1) values this
returns the number of days in C1
if the number is bigger than 2 and the word Complete has not been
enter in D1 the cell is coloured Red

How can i adapt the code so i can enter numerous values in column A and
b and return the values in column C colouring the appropriate cell in
Coloum D ?


Sub test_date()
If ThisWorkbook.Worksheets("sheet1").Range("D1").Valu e < "Complete"
And ThisWorkbook.Worksheets("sheet1").Range("C1").Valu e 2 Then
ThisWorkbook.Worksheets("sheet1").Range("D1").Sele ct
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If
End Sub