# Multiple conditional formattings for rows

#### farmertml

##### Board Regular
Hi,

I have searched the board but can't find anything which has seemed to resolve my problem.

I am currently working on a spreadsheet which for my boss. The spreadsheet requires that a row will change colour based on the value in a specific cell,I.E. "X8"

I have been trying to make the conditional formatting function work but cannot get it to work for multiple colours / values using a formula.

My main aims are :-
When the cell X8 is 3 or below I want the whole row to change Green
When the cell X8 is 10 or below I want the whole row to change Orange
When the cell X8 is 11 or above I want the whole row to change Red.

This will be for multiple rows in the spreadsheet (up to about 200). X9, X10, X11 etc.

Regards,
Paul

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Select all the rows and use a formula of

=\$X8<=3 - green
=\$X8<=10 -orange
=\$X8>10 - red

Thanks for your response! - I'm unsure what you mean with regards to the forumla, can I create multiple contional formatting formuli based on one cell? For example if I call the number in cell X8 value "Y" Can I make all those 3 formulas change the row colour based on value Y?

Thanks

Yes you can, there is a button and the foot of the dialog toi add another condition

You use formula by changing the Condition 1 dropdown from Condition Is to Formula Is.

Perfect, worked 100% thank you so much!!

Arghhh I've come up against another problem.

The maximum number of conditions is three and I now need 4!!
My boss now wants the cell to remain white (default) when the value is 0 but turn green between 1 and 3 but still change orange and red when the values are 5 + 10 respectively.

Basically it needs to achieve the following, but so the forumla actually works in excel...

0 < \$G4 <=3

Excel Workbook
ABCDEFGHIJ
11
22
33
44
55
66
777
88
99
1010
1111
1212
13
14
15
16conditionally formatted as new rule, =\$B1
17conditionally formatted as new rule, =\$B1
18conditionally formatted as new rule, =\$B1>=11 and formatted as yellow
19only problem with this is if cell b2 is empty it still formats the row green,
20but I'm sure there's a format as white if you change blank cells to zeros and copy similar to above
21
Sheet2

sorry if this has displayed a bit strangely, but the table i formatted was only the cells with colour, not the rest

I've just added a fourth condition ant it's taken it no problem, =\$B1=0 then format with a white fill and it works on both empty cells and zeros

Excel Workbook
ABCDEF
10
22
33
44
55
66
777
88
99
1010
1111
1212
131
14
Sheet2

Replies
3
Views
212
Replies
7
Views
719
Replies
1
Views
275
Replies
1
Views
802
Replies
9
Views
616

1,203,625
Messages
6,056,398
Members
444,862
Latest member
more_resource23

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

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