Conditional Formatting

JBWalker

New Member
Joined
Feb 17, 2020
Messages
5
Office Version
2010, 2007
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.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
548
Office Version
365, 2019, 2010
Platform
Windows
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

Board Regular
Joined
Sep 23, 2011
Messages
75
Office Version
365
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,100,037
Messages
5,472,112
Members
406,804
Latest member
xbinsx

This Week's Hot Topics

Top