Forum Discussion
Copying Conditional formatting with relative cell referencces in the formula desn't work
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?
It shall be relative reference for the row, e.g. Cell Value = $A1. Otherwise you always compare with the value in A1.
Jbetteridge91435 , glad to help
SergeiBaklanThankyou very much. this has saved me sooooo much time!! your the real MVP!!
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
