Copying conditional Formatting in Excel

Question

I use conditional formatting to format a cell depending on the values of another cell. This is the rule:

Cell Value not between =$BV$10*0,5 and =$BV$10*0,5

This rule is saved on Cell Y10

(Explanation: If the Value in Y10 is inbetween 0,5...1,5 * BV10 apply the formatting)

I want to use this rule on many different Cells but the formula should adjust automaticly, just like in a "normal" formula.

When I copy the format using the Format Painter the formula is copied without any changes. But I need the row to be changed. For example, when copying to Y11 the formula should be changed to :

 =$BV$11*0,5

Is there any way to achieve this or to change $BV$10 into $[this column+83]$[this row] or something like that?

Answer 1

Yes. $ is used to lock the cell reference.
So to get what you need just change your formula to this unlocking the rows:
=$BV10*0,5 and =$BV10*1 ~~> I change the limits to actually test the formula

So if you want your column to move as well, then remove the other $ sign.
Also, you can explicitly apply this formatting to other cells by supplying the range in Applies To argument like below:

Take note that you can actually put the formatting in any cell.
But the effect will always be on the Range you explicitly define in Applies to.
I discourage using the Copy Paste Format as this sometimes overlaps formattings you've done.
To apply the formatting to a group of different range, just separate the ranges with a , comma like what you see below:

So the formatting will then be applied to $Z$10:$Z$20 as well.
It is the same as $Y$10:$Z$20, but I just want to point out that it can be used to none contingous range.
And since your column is locked with your formatting, it will still be references to $BV(x) where x is the corresponding row in Y and Z.