Color cells that have a value lower than a reference value

[mcva]

Hi
I have a simple problem to solve. I have tried conditional formatting with a formula but it doesn't work. The table has 100 000 rows...
I need to color the cells on columns AE to AL of the following pivot table if the values are smaller than the reference values given by column AN. In other words for the values in row 3 the reference value on column AN is 2, and for row 4 is 3 and so on...for each row there is a different reference value in column AN. Can I do this with conditional formatting?
Thank you.

 

[myall_blues]

Use

=AND(NOT(ISBLANK(AF3)),AF3<$AN3)

as your conditional formatting formula.

Book1
	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN
2
3	A-dos-arcos			1				2	3		2
4	Alenquer		3	1					4		3
5	Averca		1						1		3
6
7
Sheet1

Cell Formulas
Range	Formula
AL3:AL5	AL3	=SUM(AF3:AK3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AF3:AL5	Expression	=AND(NOT(ISBLANK(AF3)),AF3<$AN3)	text	NO

 

[mcva]

Use

=AND(NOT(ISBLANK(AF3)),AF3<$AN3)

as your conditional formatting formula.

Book1
	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN
2
3	A-dos-arcos			1				2	3		2
4	Alenquer		3	1					4		3
5	Averca		1						1		3
6
7
Sheet1

Cell Formulas
Range	Formula
AL3:AL5	AL3	=SUM(AF3:AK3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AF3:AL5	Expression	=AND(NOT(ISBLANK(AF3)),AF3<$AN3)	text	NO

Thank you...