Forum Discussion
Copying Conditional formatting with relative cell referencces in the formula desn't work
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
SergeiBaklan 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.
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