Highlight cell if the finish date of a task is less than x days [Closed]

Report
Posts
1
Registration date
Wednesday April 9, 2014
Status
Member
Last seen
April 9, 2014
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Please could somebody help me with this problem. I have a spreadsheet and i would like make some cells to go yellow if the finish date of a paticular task is less than x days remaining form the current day????

for example i have a specific task, it starts on the 1st april and finish on the 10th april and todays date is the 5th april.
i set a function that if the task is 6 days away or less from finish date it the finish cell should go yellow

1 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
suppose data is like this (column a blank except for header =beg date)
beg date end date
13-Apr
14-Apr
15-Apr
16-Apr
17-Apr
18-Apr
19-Apr
20-Apr
try this macro

Sub test()
Dim r As Range, c As Range, x As Integer
x = 6 ' CHANGE THIS IF NECESSARY
Set r = Range(Range("B2"), Range("B2").End(xlDown))
r.Cells.Interior.ColorIndex = xlNone
For Each c In r
If c - CDate(Date) < x Then c.Interior.ColorIndex = 6
Next c

End Sub