# Conditional Formatting-Traffic Lights

#### JABWootton

##### Board Regular
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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### GlennUK

##### Well-known Member
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.

##### Well-known Member
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
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
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
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

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
It works
Thanks
Forgot to Take " Out when copying

Thanks Again
Much Appreciated

Replies
5
Views
616
Replies
3
Views
960
Replies
1
Views
658
Replies
0
Views
469
Replies
5
Views
359

1,170,942
Messages
5,872,857
Members
432,950
Latest member
ALeXceLBr

### 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.

### Which adblocker are you using?

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

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