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...
Hi Roger,
It shall be
=h19>$C$19 (not ="h19>$C$19")
I'm having the same problem, and not having quotes around my references doesn't make any difference. As near as I can tell, it's impossible to copy conditional formatting from one cell to another — by copy-paste, or paste special, or format painter — and have the pasted conditional formatting references the relative cells rather than the original cells.
Hi Rob,
Quote or not quote matter - in first case you compare two strings which are always the same, thus you always have same TRUE or same FALSE, depends on which texts to compare. In second case you compare values of the cells, result depends on how you use absolute and relative references.
You may use rule like this
You may use the rule not to entire range but for the first cell in it and after that copy formatting by format painter
In this case you generate as many similar rules as many cells in your range.
Sample attached.
I have the following conditional formatting rule in B2.
=AND(B2<=DAY(TODAY()), D2>0, G2="")
When I use Format Painter to copy it down to other rows, the relative row number remains "2".
I got it to work. I wasn't as clear as I thought I was on relative vs absolute. Thanks.
- How about posting the solution?
