please can anyone help me?
With the below code i'm trying to format the cell color when an date in a cell reaches the system date or a set number of days in advance. Lest say for 10 days from today i want it to change to blue, of all the days that are within days 10 to "5"
witch is my next set date should turn blue. so on till they reach the due date. also on the due date if i'm any date passed of today's date i want it to change to red. i can kinda get it working but have problems since the workbook won't be open ever day so i need it to calibrate the dates.
thanks for any help or advice
Dim myDate As Date
Dim rngCell As Range
Dim lrow As Long
'format the date excluding time
myDate = FormatDateTime(Now, 2)
lrow = Range("D" & Rows.Count).End(xlUp).Row
For Each rngCell In Range("D3:D" & lrow)
Select Case DateDiff("d", FormatDateTime(rngCell.Value, 2), myDate)
rngCell.Interior.ColorIndex = 46
Case Is >= 1
rngCell.Interior.ColorIndex = 3
rngCell.Interior.ColorIndex = 6
rngCell.Interior.ColorIndex = 4
rngCell.Interior.ColorIndex = 5
MsgBox "The date color sort is completed .", vbExclamation, "Color"