Previous Topic Next Topic

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

Closed
v022695 Posts 1 Registration date Wednesday April 9, 2014 Status Member Last seen April 9, 2014 - Apr 9, 2014 at 07:57 AM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Apr 10, 2014 at 07:54 AM
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

Answer 1 / 1
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Apr 10, 2014 at 07:54 AM
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
0