Conditional formatting as comparisons

Helloitsme

New Member
Joined
Feb 19, 2021
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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
ABACADAEAFAGAHAIAJAK
6Eros Eau de Parfum50SP-67356646.5760.1206101004 days4 days
7ErosEau de Parfum100SP-67356557.5471.651963653 days3 days
8Eros Eau de Parfum200SP-70702183.55103.1241081023 days3 days
9ErosEau de Toilette30SP-20832652.1164.96065005004 days4 days
Overview
Cell Formulas
RangeFormula
AG6:AG9AG6=SUM((VLOOKUP(AE6,'Stock Perfume'!E:R,12,FALSE)*1.21),8.15,2,1.74)
AI6:AI9AI6=VLOOKUP(AE6,'Stock Perfume'!E:R,11,FALSE)
AK6:AK9AK6=VLOOKUP(AE6,'Stock Perfume'!E:R,14,FALSE)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AK6:AK27Cell Value<$AJ$6textNO
AK6:AK27Cell Value>$AJ$6textNO
AK6:AK27Cell Value<$AJ$6textNO
AK6:AK27Cell Value>$AJ$6textNO
AI6:AI27Cell Value<$AH$6textNO
AG6:AG27Cell Value<$AF$6textNO
AI6:AI27Cell Value>$AH$6textNO
AG6:AG27Cell Value>$AF$6textNO
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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).
 
Upvote 0
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).
 
Upvote 0
Solution
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.
 
Upvote 0
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).
 
Upvote 0
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 :)
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top