Advanced Cell Conditionals Formatting

[selkov]

Currently i am working with a spreadsheet that has 18 columns and i am checking the value of c10 cell and then highlighting the row based on that 1 cell.
I would like to improve on this by adding a second color and a second cell reference.

My vision is to check r1c5 and r1c10.
And if c5 is > "0" then potentially set the color to "yellow" AND if c10 is < "2" color the row from c1 to c15 yellow.

The trick is I want all the following rows that have no value in c5 to color the same [only if c10 for that row is < "2" also] until c5 has another value.

Then i want to change the color to "blue" and repeat the scenario.
then on the third set go back to the yellow.

This way I can visually group a set of conditions by alternating colors.

This is beyond me. Can anyone help me work thru this or give me some thing I can try to modify to work?

Thanks -Eds

 

[Peter_SSs]

Pretty hard to follow and to know what your data and expected results are like. Would this be the correct colouring? If not, clearer explanation & examples might help.
I'm assuming there is, or can be, a heading row.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2	1	2	3	3	3	2	2	0	2	1	3	2	1	1	3	2	2	0
3	2	3	2	0		2	0	1	3	2	0	2	0	2	1	2	2	1
4	0	2	3	0		2	0	2	3	1	0	2	1	0	2	2	2	1
5	2	2	3	1		3	0	1	0	0	0	0	2	2	2	3	3	1
6	3	1	2	2		3	3	3	1	3	0	1	3	0	0	2	0	3
7	1	3	2	3	2	2	0	2	2	1	2	0	1	0	1	1	3	1
8	0	0	2	0		0	3	1	2	2	3	3	1	1	3	2	3	1
9	0	0	0	3		3	1	3	0	2	3	3	2	2	0	0	2	3
10	1	1	3	0		3	2	1	1	0	1	2	0	2	3	0	1	1
11	2	1	0	3	2	0	1	2	3	5	2	0	1	1	3	3	3	1
12	2	3	2	1		2	0	2	1	1	1	3	3	0	1	3	2	2
13	2	1	2	2		3	3	1	3	2	0	2	1	2	0	2	0	1
14	2	3	3	2		0	1	2	0	0	0	0	1	1	1	2	1	1
15	2	1	0	3	0	1	1	2	1	1	1	2	1	3	2	2	2	2
16	3	0	0	0	2	0	0	1	0	2	0	0	2	1	0	2	2	2
17	0	0	0	2	2	2	2	3	1	0	2	3	2	1	2	1	0	3
18	1	2	2	3	1	2	1	1	3	1	1	2	3	2	1	3	2	2
19	3	2	0	0	0	1	1	2	2	3	1	0	1	2	1	1	0	3
20	2	0	2	0	0	1	1	0	3	0	3	1	1	3	0	2	2	2
21	2	0	1	0	1	1	0	0	3	0	3	2	0	0	3	2	0	3
CF

 

[selkov]

Sorry that I am not clear - but I think you got it perfect....... So now how do I do that?

 

[Peter_SSs]

Sorry that I am not clear - but I think you got it perfect....... So now how do I do that?

I used column T as a helper column. That column could be hidden after you populate the formulas in it.
T1 hold a 0
T2 formula copied down as far as you might ever need.
Select A2:O?? and apply the Conditional Formatting rules shown.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1																				0
2	1	2	3	3	3	2	2	0	2	1	3	2	1	1	3	2	2	0		1
3	2	3	2	0		2	0	1	3	2	0	2	0	2	1	2	2	1
4	0	2	3	0		2	0	2	3	1	0	2	1	0	2	2	2	1		1
5	2	2	3	1		3	0	1	0	0	0	0	2	2	2	3	3	1		1
6	3	1	2	2		3	3	3	1	3	0	1	3	0	0	2	0	3
7	1	3	2	3	2	2	0	2	2	1	2	0	1	0	1	1	3	1		0
8	0	0	2	0		0	3	1	2	2	3	3	1	1	3	2	3	1
9	0	0	0	3		3	1	3	0	2	3	3	2	2	0	0	2	3
10	1	1	3	0		3	2	1	1	0	1	2	0	2	3	0	1	1		0
11	2	1	0	3	2	0	1	2	3	5	2	0	1	1	3	3	3	1
12	2	3	2	1		2	0	2	1	1	1	3	3	0	1	3	2	2
13	2	1	2	2		3	3	1	3	2	0	2	1	2	0	2	0	1
14	2	3	3	2		0	1	2	0	0	0	0	1	1	1	2	1	1
15	2	1	0	3	0	1	1	2	1	1	1	2	1	3	2	2	2	2
16	3	0	0	0	2	0	0	1	0	2	0	0	2	1	0	2	2	2
17	0	0	0	2	2	2	2	3	1	0	2	3	2	1	2	1	0	3		1
18	1	2	2	3	1	2	1	1	3	1	1	2	3	2	1	3	2	2		0
19	3	2	0	0	0	1	1	2	2	3	1	0	1	2	1	1	0	3
20	2	0	2	0	0	1	1	0	3	0	3	1	1	3	0	2	2	2
21	2	0	1	0	1	1	0	0	3	0	3	2	0	0	3	2	0	3		1
22
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =$T2=0	Abc
A2	2. / Formula is =$T2=1	Abc

 

[selkov]

I think that will. Thanks. What not thinking of following that path Thanks!

 

[Peter_SSs]

I think that will. Thanks. What not thinking of following that path Thanks!

Hope it works out for you.

 

[markmzz]

HI!

Another way (with Peter's sheet layout and without help column):

First Conditional Formatting (with Stop Here option select and without color format):

=IFERROR(MATCH(2,1/(($E$2:$E2>0)*($J$2:$J2>=2))),0)>
IFERROR(MATCH(2,1/(($E$2:$E2>0)*($J$2:$J2<2))),0)

Second Conditional Formatting (with Yellow color format):

=(($E2="")+($E2>0))*($J2<2)*MOD(COUNTIFS($E$2:$E2,">0",$J$2:$J2,"<2"),2)

Third Conditional Formatting (with Blue color format):

=(($E2="")+($E2>0))*($J2<2)*(MOD(COUNTIFS($E$2:$E2,">0",$J$2:$J2,"<2"),2)=0)

Ps: the order of CF is important for the suggestion above to work.

I hope that the formulas above helps.

Markmzz