Aug 08 2017 08:30 AM
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?
Aug 08 2017 09:07 AM
Hi Roger,
It shall be
=h19>$C$19 (not ="h19>$C$19")
Aug 04 2018 01:57 PM
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.
Aug 04 2018 02:45 PM
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.
Aug 04 2018 02:55 PM
I got it to work. I wasn't as clear as I thought I was on relative vs absolute. Thanks.
Oct 02 2018 11:02 AM
Jul 08 2019 07:47 AM
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.
Jul 09 2019 02:26 AM
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.
Jul 16 2019 04:47 AM
@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.
Jul 21 2019 07:01 AM
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.
Jul 22 2019 12:06 AM
@mrkuramotothanks for the link, I'll give it a try later :)
Jan 18 2020 12:45 PM
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.
Jan 18 2020 12:52 PM
Jan 18 2020 01:10 PM
If you compare cell to cell it shall work. Let say you apply CF to first cell only
after that double click format painter and drag the mouse over the rest of range - it'll be like
Jan 19 2020 10:12 AM
@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.
Jan 19 2020 10:27 AM
I tried using =D15>(BUDGET!D15*1.05) too. It copies the D15 cell reference as the test, instead of changing relatively.
Jan 19 2020 03:29 PM
That is correct behavior, you may check on the sample. Let for such sample
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
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
Mar 02 2020 05:53 PM
@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?
Mar 03 2020 09:27 AM
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.
Mar 05 2020 01:26 AM
@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!