Forum Discussion
Copying Conditional formatting with relative cell referencces in the formula desn't work
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.
Jbetteridge91435 , glad to help
SergeiBaklanThankyou very much. this has saved me sooooo much time!! your the real MVP!!
Yes, but first one could have side effect in some situations
Yes, looks like both methods will work provided that the first row of data is selected in the current section. All good! Thanks for sharing.
What you are showing in your reply will not work. However, the format I presented works for me in my workbook which was attached. Since I am new to this, I am not sure you see it. Anyway, good luck.
BUDGET - C ACTUAL - D 454 454 258 258 159 155 357 357 654 654 Two Rules: formulas
=D11:D15<>C11:C15 Red
=D11:D15=C11:C15 Green
Applies to: =D11:D15<>C11:C15 same in both rules
Thank you for calling out the one subtle difference that allows the format painter to retain relative references!
For those still confused, notice the differences to the rule cell values as shown below:
Original Rules
Rule (applied in order shown) Format Applies to Cell Value = $A$1 AaBbCcYyZz =$B$1 Cell Value <> $A$1 AaBbCcYyZz =$B$1 New Rules
Rule (applied in order shown) Format Applies to Cell Value = $A1 AaBbCcYyZz =$B$1 Cell Value <> $A1 AaBbCcYyZz =$B$1 Changing $A$1 to $A1 allowed the format painter to paste relative values to rest of the cells.
Thanks for you your help SergeiBaklan
It shall be relative reference for the row, e.g. Cell Value = $A1. Otherwise you always compare with the value in A1.
Okay, I'm trying these methods, and it's not working for me. In my case, I have 2 columns with values, A and B. Column A contains fixed values. Column B has values either entered manually, or from a formula.
Example:
1413
1794 82 82 37 37 324 458 For cell B1, I have the following two conditional formats applied:
Rule (applied in order shown) Format Applies to Cell Value = $A$1 AaBbCcYyZz =$B$1 Cell Value <> $A$1 AaBbCcYyZz =$B$1 So I'm chaging the text color of B1 to green when the value matches A1, or red if it doesn't match.
I need to copy both formatting rules to the rest of column B, with each cell referencing it's A column counterpart. Example of how it should look:
1413
1794 82 82 37 37 324 458 None of the formatting copy methods I've tried are working. Each one is retaining the original A1 cell value comparison.
Am I missing something, or have I overlooked a step?
