# 3 color conditional formatting formulas rules

#### Brew

##### Well-known Member
How do I create 3 color conditional formatting formulas for the following rules?:

if today is 15 days or greater before the day in the cell then green
if today is between 14 and 7 days before the day in the cell then yellow
if today is within 6 days to zero days before the day in the cell then results
anything is clear
x8:x21
5/10/2012
5/14/2012
5/21/2012
6/11/2012
6/12/2012
6/12/2012
6/12/2012
6/20/2012
6/22/2012
6/23/2012
7/4/2012
7/5/2012
7/8/2012
7/12/2012

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Not sure if the third condition got cut off, but try this:

Excel Workbook
X
805/10/2012
905/14/2012
1005/21/2012
1106/11/2012
1206/12/2012
1306/12/2012
1406/12/2012
1506/20/2012
1606/22/2012
1706/23/2012
1807/04/2012
1907/05/2012
2007/08/2012
2107/12/2012
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
X81. / Formula is =AND(X8>TODAY()+7,X8Abc
X82. / Formula is =X8>=TODAY()+15Abc

I don't think you need a condition for it to remain clear

=IF(TODAY()-X8>=15,1,0) - Green

=IF(AND(TODAY()-X8<=14,TODAY()-X8>=7),1,0) - Yellow

Last edited:
the condition is red:
if today is within 6 days to zero days before the day in the cell then Red

=IF(AND(TODAY()-X8<=6,TODAY()-X8>=0),1,0) -Red

I get the following error message while in the conditional formatting module:

"
you cannot use relative references in conditional formatting criteria for color scales, data bars, and icon sets."

excel 2007
I went to conditional formatting - new rules - format all cells based upon there values - 3 color style formate -
type formula, then i entered the red formula in first value field, yellow in 2nd value field and green in 3rd value field

Sorry I should have said..... You need to go to New Rule - Use a formula to determine format

Also once you've have done your condition, make sure you apply it to your range

please send my previous message, i got an error message

I get the following error message while in the conditional formatting module:

"
you cannot use relative references in conditional formatting criteria for color scales, data bars, and icon sets."

Below is the procedure i used
excel 2007
I went to conditional formatting - new rules - format all cells based upon there values - 3 color style format -
type formula, then i entered the red formula in first value field, yellow in 2nd value field and green in 3rd value field

You DONT use conditional formatting - new rules - format all cells based upon there values

Use

conditional formatting - new rule - Use a formula to determine format

Replies
2
Views
95
Replies
6
Views
214
Replies
10
Views
266
Replies
15
Views
347
Replies
5
Views
151

1,203,326
Messages
6,054,745
Members
444,748
Latest member
knowak87

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