Conditional Formatting-Traffic Lights

JABWootton

Board Regular
Joined
Dec 16, 2004
Messages
94
Office Version
  1. 2019
Platform
  1. Windows
Cell N13 has a formula in which calculates a Date given certain Criteria
Trying to get Cell N13 to change colours according to Date in Cell N13
If Date in Cell N13 is less than Today then it goes Red
If Date in Cell N13 is more than 120 Days Away then it goes Green
If Date in Cell N13 is less than 120 days away then it goes Orange

Can seem to get simple formula to work
Thanks in Anticipation
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
You haven't given the formula of N13. I assume you are entering formulae in the Conditional Formatting conditions too, but you haven't shown those either.
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

is it

Condition 1 (Formula is)

Code:
=$N$13<TODAY()

Condition 2 (Formula is)

Code:
=$N$13>TODAY()+120

Condition 3 (Formula is)

Code:
=$N$13<TODAY()+120

format as required
 

JABWootton

Board Regular
Joined
Dec 16, 2004
Messages
94
Office Version
  1. 2019
Platform
  1. Windows
Sorry
Formula in Cell is
=IF(AND(B13="Y",ISNUMBER(SEARCH("GCD",C13)),ISNUMBER(J13),OR((K13=1),(K13=2))),J13+180,"")

Adds 180 days to Date entered in Cell J13 if Y in Cell B13 and "GCD" is somewhere in Cell C13

Hope to enter formulas in 3 Conditions but only entering following

=(N13<TODAY()) Turns cell Red

=(N13>=TODAY()) Turns Cell Green

Unable to Do the one that will change it Orange
Think that the 2 formulas alreay entered are wrong thus causing problem
Hope this Helps
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

And watch out because if the date is exactly 120 days away you won't get any colour (conditional) formatting returned. Hence, you might like to amend OaD's final formula to:

Code:
$N$13<=TODAY()+120

Richard
 

JABWootton

Board Regular
Joined
Dec 16, 2004
Messages
94
Office Version
  1. 2019
Platform
  1. Windows
Hello,

Condition 1 (Formula is)

Code:
=$N$13<TODAY()

Condition 2 (Formula is)

Code:
=$N$13>TODAY()+120

Condition 3 (Formula is)

Code:
=$N$13<TODAY()+120

format as required

Thanks
Dosent Seem to work
Cell Changes Green OK
Also Changes to Orange OK
But not to red

Assume Reading Correct
Condition 1 Changes Cell to Red
Condition 2 Changes Cell to Green
Condition 3 Changes Cell to Orange

Do I need to enter them in this order in the 3 Conditions
 

JABWootton

Board Regular
Joined
Dec 16, 2004
Messages
94
Office Version
  1. 2019
Platform
  1. Windows
It works
Thanks
Forgot to Take " Out when copying

Thanks Again
Much Appreciated
 

Forum statistics

Threads
1,136,420
Messages
5,675,742
Members
419,585
Latest member
popsin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top