Conditional formatting - changing cell reference

[MufasaFury]

First post - thank you for any help you can give.

I have a row of cells from A1:G1, and I want them to highlight in red if they're below cell I1, and green if they're above cell J1. Did that - easy.

Now I want to do the same thing with the same columns, but changing the rows (I2 and J2 are different than I1 and J1).

Is this possible without conditionally formatting over and over?

[FDibbins]

Hi, welcome to the forum

So you will have 4 rules?

Or do you just ant to change where the rule references (if so, maybe just move/drag the 1st 2 refs down?)

[Monimonika]

I am assuming that by "below cell I1" and "above cell J1" you mean below/above the values inside those cells.
Have the "Applied to" ranges for each condition have cell $A$1 as the upper left-most cell, then you could enter the custom condition formulas as:

=A1<$I1 for red
=A1>$J1 for green

The dollar sign makes the I and J columns absolute and unchanging no matter where the formula is applied. The columns/rows without the dollar signs change relative to where they are applied.
So for cell B2, the applied condition will be " =B2<$I2" although it would be a part of the "=A1<$I1" rule.

[MufasaFury]

wow - such a simple solution... thanks! You'd think I would've noticed the dollar signs in the conditional formatting rules menu

[FDibbins]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

I note also that you dont show where you are from. While we dont really need the exact location, it does help if we know the country - different counties use different formats, which we wont know if you dont show us