Copying Conditional formatting with relative cell referencces in the formula desn't work

Copper Contributor

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?

 

60 Replies

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

image.png

 

You may use the rule not to entire range but for the first cell in it and after that copy formatting by format painter

image.png

In this case you generate as many similar rules as many cells in your range.

Sample attached.

I got it to work. I wasn't as clear as I thought I was on relative vs absolute. Thanks.

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.

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.

@abigail_nottingham 

 

That's a normal behaviour. Dragging Format Painter from T4 to T14 you apply conditional formatting to entire range T4:T14. Excel take formula for the first cell in the range as the basis and apply it internally to each cell in the range. Exactly the same will be if you first select T4:T14 range, add rule formula for the first cell, e.g. T4>A4 and apply to entire range - formula itself isn't changed if you check CF for any other cell within the range.

@Sergei Baklan, it may be normal behavior, but it seems that if the feature allowed for relative references in the range field that you would be able to paint a much larger range at a time and preserve the proper reference.  Without that, I'm having to paint one row at a time, 500 times.

@Jim_R 

 

Hello Jim,

Maybe you already solved what you were looking for. If not, please visit this website.
With just a few clicks it is possible to set the conditional format for thousands of lines. The first example in this website is "crazy" at first glance but it works smoothly. It solved my problem! and there, the author offers several good examples of conditional formatting. 

https://exceljet.net/conditional-formatting-with-formulas

@mrkuramotothanks for the link, I'll give it a try later :)

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.

Nope. That site DOES NOT discuss the USE CASE that several of us are asking about here in this thread.

@varocketry 

If you compare cell to cell it shall work. Let say you apply CF to first cell only

image.png

after that double click format painter and drag the mouse over the rest of range - it'll be like

image.png

@Sergei Baklan    I want the original (top left) cell to move relatively, not be fixed.  Let me try the way....
I have two sheets in the spreadsheet, each contains an identical BUDGET . (12 MOnths across top and 30 budget categories along the left side.)
They are identical now when I create the budget, but one will be used to record ACTUAL expenses as the year progresses. I want to use COnditional Formatting on each cell, to highlight when that cells amount exceeds (>) the Budget Sheet's value for that same cell.

I use formula =D15>(BUDGET!$D15*1.05) and then highlight red.

When I use Format Painter across a row or column, EXCEL fixes that original D15 reference.
I want the test cell reference to change relatively too.

Does this explain my question, is there a solution for me?  I am now doing in manually one cell at a time.

I tried using    =D15>(BUDGET!D15*1.05)    too.   It copies the D15 cell reference as the test, instead of changing relatively.

@varocketry 

That is correct behavior, you may check on the sample. Let for such sample

image.png

we apply CF rule as in your post only the the cell D15 and as the next step apply by Format Painter to all other cells in our ranges. Rule formula will be

image.png

It is still D15 in rule formula but that's what we need. Engine scans Applies to ranges one by one cell and apply rule formula same way as if you drag D15 cell here. Other words , for F15 formula works as =F15>Budget!F15; for F16 as =F16>Budget!F16, etc.

 

If we change initial formula on =D15>Budget!$D$15*1.05 we always will be compare with Budget!$D$15, e.g. on next range F15 will be cvompared as =F15>Budget!$D$15.

 

Of course, if do not "paint" by Format Painter but double click on it and navigate with that through range cells, we will generate a lot of new rules with "correct"cell references

image.png

 

@Sergei Baklan I am trying to apply your instructions in relation to below as that is what I am trying to achieve.  I did a direct double click (eg not paint brush) and expanded the range, but it did not generate new rules...any suggestions?

 

  • Of course, if do not "paint" by Format Painter but double click on it and navigate with that through range cells, we will generate a lot of new rules with "correct"cell references

@drn123 

I meant Format Painter in both cases after you double click on it on the cell with applied rule. After that

1) Click on first cell of the range, hold the button pressed and move mouse over the range, after that release the button. One new rule will be generated.

2) Click on first cell of the range and release the button. By arrows navigate through other cells of the range. New rule for the each of such cells will be generated.

 

After all Esc to exit from Format Painter.

@Sergei Baklan - thanks for this, i've had the same issue this week- trying to copy conditional format to hundreds of cells but needing the reference cell to change each time also. 

 

the answer above of double click and then using arrows resolved this for me. thank you!