Multiple conditional formattings for rows

[farmertml]

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

 

[xld]

Select all the rows and use a formula of

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

 

[farmertml]

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

 

[xld]

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.

 

[farmertml]

Perfect, worked 100% thank you so much!!

 

[farmertml]

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

 

[scottylad2]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1		1
2		2
3		3
4		4
5		5
6		6
7		77
8		8
9		9
10		10
11		11
12		12
13
14
15
16	conditionally formatted as new rule, =$B1
17	conditionally formatted as new rule, =$B1
18	conditionally formatted as new rule, =$B1>=11 and formatted as yellow
19	only problem with this is if cell b2 is empty it still formats the row green,
20	but I'm sure there's a format as white if you change blank cells to zeros and copy similar to above
21
Sheet2

 

[scottylad2]

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

 

[scottylad2]

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

 

[scottylad2]

Excel Workbook
	A	B	C	D	E	F
1		0
2		2
3		3
4		4
5		5
6		6
7		77
8		8
9		9
10		10
11		11
12		12
13		1
14
Sheet2