# 3-color conditional formatting using formula

#### ismoo1

##### New Member
Hi All!

3-color conditional formatting using formula

I have an issue how to create 3-color formatting based on formula relying onto few cells values, but cannot get it working nor found help from colleagues/google

I enter formulas via "conditional formatting" -> "color scales! -> "format all cells based on values" -> "3-color scale"
- As type I select "formula"

It seems that formula's has no whatsover effect regardless whatever I enter, also wonder as it approves only fixed references how could it work for several rows?

Col1 Col2
2 1
3 2
4 3

Green, if Col1>3 ot Col2 >1
Red if Col1<3 and Col2<2
Yellow if Col1<3 and Col2>1

Ismo

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
What results are you expecting?
Your first row will have no effect since none of the three formulas are TRUE. (GREEN: 2 is not > 3, 1 is not > 1, RED: 2 is less than 3 but 2 is not less than 1, YELLOW: 2 is less than 3 but 1 is not > 1 hence no effect).
Your second row will be green
Your third row will be green

You typed "ot" in one formula, I presume thats meant to be OR ?

Try this

Select A1:B3

Conditional Formatting
New Rule
Use a formula to determine...
3 formulas needed

=OR(A1 > 3,B1 > 1)
format as green

=AND(A1 < 3,B1 < 2)
format as red

=AND(A1 < 3,B1 > 1)
format as yellow

Format as required

Last edited:
Thanks SpecialK99!

Sorry for my poor question statement, but picked up the key point from your answer being able to have several nested formulas in sequence and was able to achieve desired output after some iteration.
Excel Formula option in 3-color different limits was really misleading

Replies
3
Views
716
Replies
3
Views
421
Replies
15
Views
1K
Replies
2
Views
371
Replies
3
Views
328

Threads
1,196,295
Messages
6,014,519
Members
441,826
Latest member
roudarreza

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

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