Forum Discussion
Solved
Conditional formatting with 3 color scale. Copy format to other cells
Dear experts, How can I copy the conditional formatting of the 1st row to subsequent rows taking into account the values of the corresponding row and not the 1st one. Thanks in advance for yo...
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
Thanks for your proposals!
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.
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.
Thank you so much for your support, but I am doing something wrong.
Would you be so kind having a look at the steps:
1. File--> Options --> Formulas --> Working with Formulas --> R1C1 Reference Style.
2. Conditional formatting for cell A2 considering values of C2 and D2:
3. Use Format painter to copy formula
4. Check formula for row 11:
5. Remove tick in file options
6. check formula again for row 11 and there the reference is unfortunately again to row 2
NikolinoDE, can you please show me where I am doing the mistake?
Many Thanks,+
Jutta
Assume all cells have absolute references.
- File--> Options --> Formulas --> Working with Formulas --> R1C1 Reference Style - tick the box – Ok – back to the sheet. Now they are relative reference values.
- Select the cells/range --> Copy with Ctrl + C
- Go to the beginning cell of the desired cell/range where to paste the copied cell/s - Ctrl + V to paste.
- Go to File--> Options --> Formulas --> Working with Formulas --> R1C1 Reference Style - tick the box out – Ok – back to the sheet.
- After that, all should be absolute reference values again, with the application referring to the new cells.
That's what I have in mind...if it doesn't work for you, I recommend that you send the file (without sensitive data) or as a private message (if you think you absolutely have to) to me in order to to see how the file is structured.
Otherwise you and I will have to spend far too much time, which shouldn't be the case :).
- Since I do not see when I have to do the step with the condition formatting, I sent you the excel.