VBA date

lynng Posts 11 Registration date Thursday March 24, 2016 Status Member Last seen March 29, 2017 - Mar 29, 2017 at 11:14 AM
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

Option Explicit
Sub ChangeColor()
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)
Case 0
rngCell.Interior.ColorIndex = 46
Case Is >= 1
rngCell.Interior.ColorIndex = 3
Case 2
rngCell.Interior.ColorIndex = 6
Case 5
rngCell.Interior.ColorIndex = 4
Case 10
rngCell.Interior.ColorIndex = 5
End Select

MsgBox "The date color sort is completed .", vbExclamation, "Color"
Exit Sub

End Sub