Conditional Formatting


Hi All,

I have one question regarding the conditional formatting.

I have applied a conditional formatting on one row with 1 variable.

Can we applied the same conditions on another rows but with different variables.

Please refer to my attached files for more info.

Rule used:

If values is greater than row AA, highlight it red else highlight it green.

But when I applied the same format on another row, the variable AA remains static.



Selected Answer

Hi Naqiym

The "...variable AA remains static..." because the default cell/range selection in conditional formatting gives absolute references i.e. there's a $ sign to fix the column and a second dollar sign to fix the row e.g. $AA$2. In your case you want only the column to be fixed but the row to change between rows (like if you copied formula to other rows). That requires $AA2 (e.g.).

To do that in your sheet,

  1. Select a cell in row 2 and in the ribbon go Home/Conditional Formatting/Rules Manager...
  2. Pick first rule (pink) in the list under Rule (applied in order shown) then click Edit Rule. It says Cell Value... greater than  =$AA$2 
  3. Change the last box to read:
     then click Ok.
  4. Back in the Rules Manager, for that rule, change "Applies to" from =$B$2:$Y$2 to
     - this range can be fixed. Click Apply (You will see pink formatting appear in many cells)
  5. Do the same for the second (green) rule but I suggest you change it from "less than" to "less than or equal to" (so you don't have white cells if any value matches that in AA). In the file attached, I've changed it to that test.
  6. Carefully delete the other two rules and click Ok. (You will see green formatting also appears in many cells).

That's it!

Hope this helps (please check the attached revised file to see the results).



P.s. the bits in bold above are just to highlight where the changes are to be made- you can't make them bold in the conditioanl formatting rule itself!
John_Ru (rep: 502) Jan 6, '21 at 5:01 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login