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...
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.
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
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.
SergeiBaklan, 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.
