Conditional formatting as comparisons

[Helloitsme]

I'm trying to use conditional formatting to compare a static value with a dynamic value that will be updated daily. Whenever I try to use the conditional formatting, it compares to the first cell chosen rather than the one it should compare to. The table will contain 50 items if not more.

Please see below, AG, AI, and AK are the dynamic values:

Kollektion.xlsx
	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
6	Eros	Eau de Parfum	50	SP-673566	46.57	60.1206	10	100	4 days	4 days
7	Eros	Eau de Parfum	100	SP-673565	57.54	71.6519	63	65	3 days	3 days
8	Eros	Eau de Parfum	200	SP-707021	83.55	103.124	108	102	3 days	3 days
9	Eros	Eau de Toilette	30	SP-208326	52.11	64.9606	500	500	4 days	4 days
Overview

Cell Formulas
Range	Formula
AG6:AG9	AG6	=SUM((VLOOKUP(AE6,'Stock Perfume'!E:R,12,FALSE)*1.21),8.15,2,1.74)
AI6:AI9	AI6	=VLOOKUP(AE6,'Stock Perfume'!E:R,11,FALSE)
AK6:AK9	AK6	=VLOOKUP(AE6,'Stock Perfume'!E:R,14,FALSE)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AK6:AK27	Cell Value	<$AJ$6	text	NO
AK6:AK27	Cell Value	>$AJ$6	text	NO
AK6:AK27	Cell Value	<$AJ$6	text	NO
AK6:AK27	Cell Value	>$AJ$6	text	NO
AI6:AI27	Cell Value	<$AH$6	text	NO
AG6:AG27	Cell Value	<$AF$6	text	NO
AI6:AI27	Cell Value	>$AH$6	text	NO
AG6:AG27	Cell Value	>$AF$6	text	NO

 

[Joe4]

You may need to use the Formula option of Conditional Formatting to make the rules a little more dynamic.
If you would like us to help you with that, please provide more detail, i.e. give us the first few iterations, what cell should be compared to what cell (be sure to use specific cell addresses in your explanation).

 

[StephenCrump]

Yes, it's not totally clear what the question is ...

but I'm guessing that you want your conditional formatting formulae based on row 6 and not $6, e.g.

AK6:AK27 Cell Value <$AJ6 (not $AJ$6).

 

[Helloitsme]

You may need to use the Formula option of Conditional Formatting to make the rules a little more dynamic.
If you would like us to help you with that, please provide more detail, i.e. give us the first few iterations, what cell should be compared to what cell (be sure to use specific cell addresses in your explanation).

There's 2 different formattings here:

For example, if AG6 is a greater number than AF6, AG6 should be red, which is correct in the XL2BB above. If AG6 is lesser than AF6, AG6 should be green.

Then there's the reverse one in AI and AH. If AI7 is greater than AH7, AI7 should be green. If AI7 is lesser than AH7, AI7 should be red.


So if AG is a greater number than AF, AG should be red. if AG is a lesser number than AF, AG should be green.
Same with AK compared to AJ.
The reverse for AI compared to AH.

 

[Joe4]

There's 2 different formattings here:

For example, if AG6 is a greater number than AF6, AG6 should be red, which is correct in the XL2BB above. If AG6 is lesser than AF6, AG6 should be green.

Then there's the reverse one in AI and AH. If AI7 is greater than AH7, AI7 should be green. If AI7 is lesser than AH7, AI7 should be red.


So if AG is a greater number than AF, AG should be red. if AG is a lesser number than AF, AG should be green.
Same with AK compared to AJ.
The reverse for AI compared to AH.

See StephenCrump's comments above.
It looks like you are using an absolute reference ("$") to lock the row to check to row 6 for EVERY row.
If you remove the "$" in front of the 6, it will allow the row reference to float (so that row 7 will look at AJ7, row 8 will look at AJ8, etc).

 

[Helloitsme]

Yes, it's not totally clear what the question is ...

but I'm guessing that you want your conditional formatting formulae based on row 6 and not $6, e.g.

AK6:AK27 Cell Value <$AJ6 (not $AJ$6).

You're a champ, worked like a charm! Thank you

 

[Helloitsme]

See StephenCrump's comments above.
It looks like you are using an absolute reference ("$") to lock the row to check to row 6 for EVERY row.
If you remove the "$" in front of the 6, it will allow the row reference to float (so that row 7 will look at AJ7, row 8 will look at AJ8, etc).

Thanks for the explanation, really appreciate it so I won't run into this in the future