Forum Discussion
Copying Conditional formatting with relative cell referencces in the formula desn't work
I have a cell H19 conditionally formatted "Format values where this formula is true" with: ="h19>$C$19" and then the colour green When I copying the cell with Format Painter to cells H20 to H90, the...
Dear, thanks for taking your time to exactly trace out the errors in the formula. This formula actually works as planned but the relative reference doesn't change the cell reference by copying down our recipe. I think there is a glitch which we aren't able to catch, due to which this happens. 😉
If at any time you're able to achieve the same with relative reference changing on the given cell, Kindly share the same.
Thanks once again.
Hi,
After hours of testing, something finally worked, and at least for me it vas simpler than I imagined. I'm quite sure I tested the solution many times before to no avail, but suddenly, and repeatedly it worked:
1) Create the conditional formula in one cell, which in my case was a simple "Make red if value in this cell (A2) is larger than in the adjacent cell (B2)". In the edit formula which now refers to cell $B$2, press F4 repeatedly until you have only relative references, i e without any '$' before row or column. (If you know what you are doing you could have a mix of absolute column and relative row, or vice versa, but everything relative without ANY '$' seemed safest for me)
2) Select the cell with the formula, then click the format painter.
3) Click the first cell in the range where you want a copy of the formula, drag over the rest of the range, then release the mouse button.
It seemed to me as every cell with the same conditional format then looked relative for its comparison.
I have edited the $B$2 reference manually several times, but with varying success. Using F4 repeatedly seemed to have some magick (don't understand why) and suddenly it worked.
After hours of testing, something finally worked, and at least for me it vas simpler than I imagined. I'm quite sure I tested the solution many times before to no avail, but suddenly, and repeatedly it worked:
1) Create the conditional formula in one cell, which in my case was a simple "Make red if value in this cell (A2) is larger than in the adjacent cell (B2)". In the edit formula which now refers to cell $B$2, press F4 repeatedly until you have only relative references, i e without any '$' before row or column. (If you know what you are doing you could have a mix of absolute column and relative row, or vice versa, but everything relative without ANY '$' seemed safest for me)
2) Select the cell with the formula, then click the format painter.
3) Click the first cell in the range where you want a copy of the formula, drag over the rest of the range, then release the mouse button.
It seemed to me as every cell with the same conditional format then looked relative for its comparison.
I have edited the $B$2 reference manually several times, but with varying success. Using F4 repeatedly seemed to have some magick (don't understand why) and suddenly it worked.