Conditional Formating - Copy/Paste/Relative Reference

Hi there,

Having a bit of a nightmare with copying conditional formats. I have changed the formulas to relative reference but it is still copying the formula with the same column reference as the cell im copying from.

E.g. =A1>A2 copied across to B1:Z1 still has the =A1>A2 formula when it should change to =B1:B2 for the B column and so on....

Can someone please help me with this?

many thanks,

When looking at the Formula in the Conditional Formatting Rules Manager...
The formula is shown relative to the top/left cell of the 'Applies To' Range, regardless of what cell/column/row you have selected.
So when you see the formula, and the 'Applies To' Range is A1:Z1, then the formula is shown as it would appear in A1 (even if you have say G1 selected).
But you can rest assured, the formula follows the rules according to the absolute/relative references you applied in the formula.
So in B1, it is indeed doing =B1>B2, and C1 is doing C1>C2, etc..


The important question to ask yourself is "Does it work" ?
Do the cells in A1:Z1 change format as desired when the appropriate cells change values ?

Agree 100%. That was the confusing part because the formula doesnt change when you copy it across so the real test is whether its actually working. I will go ahead and check it now but it all seems to be working as it should now.

Thanks for the lesson on conditional formatting!

You're welcome.