Copying Conditional Formatting referencing a different cell

Copper Contributor

Is it possible to copy a conditional formatting changing the reference cell each time:

 

Example:

  I want to highlight the value whenever the cell value of F6 is not between -G6 and G6.

  then I want to go to the next row have the same thing happen using F7 is not between -G7 and G7

8 Replies

@Sjohnson1975 

You can use the Format Painter to copy all formatting, including the conditional formatting rules.

Make sure that your rule refers to -G6 and G6, not to -$G$6 and $G$6.

 

Alternatively, select F6, then click Conditional Formatting > Manage Rules... on the Home tab of the ribbon.

Expand the Applies to range from =$F$6 to - for example - =$F$6:$F$100.

Again, make sure that the rule refers to -G6 and G6, not to -$G$6 and $G$6.

@Hans Vogelaar 

 

This is the rule I have created:

Sjohnson1975_0-1711572944382.png

When I copy it to the next one I get this:

Sjohnson1975_1-1711573036471.png

Sjohnson1975_2-1711573067145.png

 

I am not sure what is going wrong.

 

 

 

@Sjohnson1975 

The rule doesn't change - it shows how it applies to the first cell in the range.

But the formula ="k6" should be =K6

Anyhow, you should apply this rule to a range in another column. There is no point in comparing K6 to K6 itself

@Hans Vogelaar 

 

I am comparing the range of the cell done negative value to positive value, -40 to 40. Wanting the cell to highlight if the result is outside that range. The range will change with every row. I am not putting the “” around the cell excel is doing that automatically when I close out of the conditional formatting page 

@Sjohnson1975 

Which column contains the cells you want to highlight?

Which column contains the values you want to compare to?

@Sjohnson1975, you are comparing K6 to K6, that's why it's wrong. (Applies to =$K$6:$K$7).

You should apply conditional formatting to a different range, let's say $J$6:$J$7.

Check my screens.

 

Let me know pls if that works for you. :smile:

 

Zrzut ekranu 2024-03-28 000958.pngZrzut ekranu 2024-03-28 001649.png

 

@Hans Vogelaar 

 

The column I want to highlight is f and the one i want to compare to is g, from negative of that number to positive.

@Sjohnson1975 

If you want to highlight values outside the range from -Gn to +Gn:

HansVogelaar_0-1711625358782.png

See the attached workbook.