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 formula doesn't change the relative reference to h20, h21.. h90 etc.. It stays as h19.
What am I doing wrong?
- Dragging the cell, pasting normally OR pasting the format in multiple cells in one paste operation won't work. It will keep the formula in conditional formatting referencing to copied cell and will make it applicable to the range of pasted cells.
I found a simple workaround.
Copy the cell with conditional formatting and paste it into the cell below it. Then select these two cells, copy them and paste them to the next two cells below; and then select these four cells and copy them to the next 4 cells below. Next, you will have 8 cells to copy and paste, next you will have 16 cells to copy and paste; every time doubling the cells available to be copied.
With this workaround, the conditional formatting formula will refer to the cell reference where you have pasted it . Roger Dance I spent hours trying to figure this out. I finally came across a video that actually gave me the information I was looking for:
CStendardo I watched that video, thanks. And the one after it on 8 COnditional Formatting Tips and was able to work out the issue . Success.
Best tip from video was to craft the conditional test in an adjacent cell to determine if it evaluates TRUE or not. Then copy that working formula to the window inside Conditional Formatting to enter the formula correctly.
I was able to select a column of number and then enter the conditional formatting function successfully. I then selected the working formatted column's rows and used the PAINTER icon tool to copy that formatting to other columns.
- Bookmarked! Thank you!
I just figured this out. If you copy the cell with conditional formatting and paste it TO ONE OTHER CELL it retains the conditional formatting and changes it relative to the cell.
If you copy and paste it TO A RANGE of cells, it does NOT change the cell relative to the others.
This means you have to ctrl+C/V a bunch of times to do a range, but it takes less time than going through the conditional formatting section and updating and achieves what you want.- I'm copying this type of formula to other cells, but still Conditional formatting is not referring to Relative reference . Can anyone help me to copy this type of color format to any number of cells by using format painter or any other.
Make sure there are no anchors ($) in the formula preventing you from copying the format to different cell rows or columns.
I know this is an old thread, but for anyone reading - I found that I was unable to copy the formatting to more than one cell at a time. The Format Painter will work copying the conditional formatting with relative cell references from, for example, cell T3 to cell T4, however, if I drag the Format Painter down cells T4 through T14, it will only use relative cell reference for T4 but not the other cells.
I think this is a feature that could use some work, if anyone else knows a way around this please let me know.
I have the same issue and frustration with the Format painter solution offered.
I want one cell in Sheet A compared to one cell sheet B and color changed if sheet A cell is > Sheet B cell.
The PAINTER solution applies a fixed reference to the range you paint it across. I know I can do it one at a time, but I have almost 400 cells in the BUDGET to compare against forecast.
Same frustration as several others above detail.
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.
