dates

[kevcar40]

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").Value <> "Complete"
And ThisWorkbook.Worksheets("sheet1").Range("C1").Value > 2 Then
ThisWorkbook.Worksheets("sheet1").Range("D1").Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If
End Sub

 

[JE McGimpsey]

One way:

Public Sub test_date2()
Dim rCell As Range
With ThisWorkbook.Sheets("sheet1").Range("D1" & _
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

 

[kevcar40]

thanks for reply
i have tried this code and found that it only fills the cell D1
if i have another value bigger than 2 in row 3 or 4 etc it does not
colour the cell
any idea how i can get it to read the columns in C an D and colour if
necessary

thaks again

kevin

 

[JE McGimpsey]

If you have nothing already in the cells in column D, change the "D1"
and "D" to "C1:C" and "C", respectively. That will run the code on every
row for which there's a value in column C.