Forum Discussion
Copying Conditional formatting with relative cell referencces in the formula desn't work
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
Thanks SergeiBaklan
I have read the information through and I find it useful, because I had to also conditional formatting on series of infinite cells. I tried the formulas mentioned in this conversation and after few trials it worked! 🙂
I don't understand exactly how it works but it does what I want.
Thanks again.
HB , glad it helped, thank you for the feedback
I've come back to this mid-year to update the spreadsheet some for my wife.
I am new to conditional formatting but have tried to follow the example - even typing your example letter by letter in the CF Rule Manager.
It' not working for me.
Rule
Formula ="d13>BUDGET!D13"
Format
RED text, red Fill
Applies to:
Summary!$D$13:$D$52,Summary!$E$13:$E$52,Summary!$F$13:$F$52,Summary!$G$13:$G$52,Summary!$H$13:$H$52,Summary!$I$13:$I$52,Summary!$J$13:$J$52,Summary!$K$13:$K$52,Summary!$L$13:$L$52,Summary!$M$13:$M$52,Summary!$N$13:$N$52,Summary!$O$13:$O$52
Nothing happens on the Summary Sheet cells that I am trying to CF.
Perhaps you see something obvious [to you] that might assist me.
SergeiBaklan 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
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.
