VBA date [Closed]

Report
Posts
11
Registration date
Thursday March 24, 2016
Status
Member
Last seen
March 29, 2017
-
Hello,
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
Next

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

End Sub