Conditional Formatting help.

[serge]

I'm trying to highlight the numbers in the 3rd table ( Green numbers ) from Column Y:AZ according to the table in the middle from O:W.

The 3rd table is the same as the second one, the only different is that the numbers are organize from smallest to largest, but I can't figure it out how to do it.

Thank you for help.
Serge.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1					ROOT Number										ROOT Number Pool Depth										Pool Depth From
2					1	2	3	4	5	6	7	8	9		1	2	3	4	5	6	7	8	9
3
4	15		6							6					10	1	11	7	24	15	2	3	4		1	2	3	4	7	10	11	15	24
5	9		9										9		11	2	12	8	25	1	3	4	5		1	2	3	4	5	8	11	12	25
6	3		3				3								12	3	13	9	26	2	4	5	1		1	2	3	4	5	9	12	13	26
7	17		8									8			13	4	1	10	27	3	5	6	2		1	2	3	4	5	6	10	13	27
8	10		1		1										14	5	2	11	28	4	6	1	3		1	2	3	4	5	6	11	14	28
9	13		4					4							1	6	3	12	29	5	7	2	4		1	2	3	4	5	6	7	12	29
10	27		9										9		2	7	4	1	30	6	8	3	5		1	2	3	4	5	6	7	8	30
11	7		7								7				3	8	5	2	31	7	9	4	1		1	2	3	4	5	7	8	9	31
12	4		4					4							4	9	6	3	32	8	1	5	2		1	2	3	4	5	6	8	9	32
13	5		5						5						5	10	7	1	33	9	2	6	3		1	2	3	5	6	7	9	10	33
14	7		7								7				6	11	8	2	1	10	3	7	4		1	2	3	4	6	7	8	10	11
15	8		8									8			7	12	9	3	2	11	1	8	5		1	2	3	5	7	8	9	11	12
69 Root 1 N

 

[konew1]

select the range of the 3rd table and create conditional format with formula =OFFSET($N4,0,SUM($E4:$M4))=Y4

 

[serge]

Thank you so much Konew1, your formula work like a charm.

Regards...
Serge.

 

[Peter_SSs]

Given that Conditional Formatting itself is volatile, I'm not sure if using the volatile OFFSET function in it will make performance any worse, but I think you should also be able to use this in your CF (selecting the same range konew1 suggested).

=INDEX($O4:$W4,$C4)=Y4

 

[serge]

Hi Peter,

I learn something new with your post about the OFFSET function been a" Volatile function ".

I also appreciate your help with your INDEX the formula.

Thank you very much.

Best Regards...

Serge.