Forum Discussion
Conditional formatting with 3 color scale. Copy format to other cells
Sorry, I didn't test properly. It works differently compare to couple of years ago. Workaround could be
- apply the rule to $A$2 only
- using format painter apply to other cells but A3
- in CF manager duplicate rule for A2 and apply it to A3
If I use the format painter, the formula always maintains the original cell reference.
But I want to have a relative reference which is not allowed in the conditional formatting.
Since the real excel has thousands of rows, I don't want to adjust the references in the formula manually.
In your conditional formatting, you have absolute references.
For the sake of simplicity, you would have to convert these into relative references.
There's an old trick I use where I switch the columns in R1C1 reference type.
This makes it possible to directly use or copy/paste the conditional formatting formulas
per code Z1S1 reference type without having to switch to the settings.
After that you can remove the tick and the formulas are adjusted to their place with absolute references to the new cells.
File--> Options --> Formulas --> Working with Formulas --> R1C1 Reference Style.