Conditional Formatting

[JBWalker]

If i have a list of specific dates E5, E6, E7 etc and I have =Today() in J1

Is it possible make specific fields B5:R5, B6:R6, B7:R7 etc, change colour depending on the timeframe between E5, E6, E7 and J1

Looking for 3 seperate colours;
2 weeks prior to specific date
1 week prior to specific date
Any day after specific date

Is this done in conditional formatting? Any assistance would be greatly appreciated.

 

[braindiesel]

Select B5:R7... B5 is the active cell
Home Cond Formatting... New rule... rule from formula (6th option)
=($E5-$J$1)>=14
Choose your colour from Format Button
Repeat steps for >=7 for one week
Finally =($E5-$J$1)<0 and choose color

in all three you could put TODAY() instead of $J$1 and that will work plus free up the cell

Hope this helps

 

[mamady]

Hi Dear,

Yes indeed, this can be achieved using conditional formatting. Please try the following steps for "2 weeks prior to specific date" scenario, then you can replicate for other scenarios and ranges:

• Select the cells you want to format B5:R5
• On the Home tab, in the Styles group, click Conditional formatting > New Rule…
• Enter the formula in the box = J1-E5>14
• Click the Format… button to choose your custom format (switch between the Font, Border and Fill tabs and play with different options)
• Click OK

to make sure that the conditional formula is applicable to the entire selected cells

• On the Home tab, in the Styles group, click Conditional formatting > Manage Rule…
• Make sure that the range under Applies to box is B5:R5
• Click OK

Best Regards
M. Yusuf

 

[JBWalker]

Brilliant. Thank you very much!

 

[braindiesel]

Happy to help

 

[JBWalker]

I'm not sure what is happening. You can see the image and the formulas i have inputted, however it does not seem to be working correctly. I would simply like the whole line (eg. B5:O5) to change colour depending on time remaining for task to be completed. Over two weeks away from due date, nothing. Between 2 - 1 week(s) then light orange. In the final week (7 days before due date) dark orange. After the due date has passed then yellow.

I've been staring at this for way too long now and cannot seem to get it all to work properly. In the example I am using the same formulas for Task 1 -Task 6.

Any help would be greatly appreciated.

 

[braindiesel]

Change your >14 to >7
and your >7 to either >0 or >=0 depending on how you want 0 handled