Forum Discussion
JuttaWERU
Jan 31, 2023Copper Contributor
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...
- Feb 13, 2023
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
JuttaWERU
Jan 31, 2023Copper Contributor
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
NikolinoDE
Jan 31, 2023Gold Contributor
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 :).
- JuttaWERUJan 31, 2023Copper ContributorSince I do not see when I have to do the step with the condition formatting, I sent you the excel.
- NikolinoDEFeb 01, 2023Gold Contributor
Sorry didn't notice that you use a color scale.
You cannot use relative references in conditional formatted criteria for color scales, data bars, or icon sets.
You can change "Applies to" with R1C1 but no references in the reference cells.
This is only possible if you don't use color scales, data bars or icon sets in conditional formatting.
For your project, I can blindly agree with Mr. Sergei Baklan’s proposed solution :))
Attached are Mr. Sergei Baklan’s formulas, translated into German.
Add these as instructed by Mr. Baklan.
Minimum
=BEREICH.VERSCHIEBEN($C$2; ZEILE()-ZEILE($A$1)-1; 0)
Mittelpunkt
=(BEREICH.VERSCHIEBEN($B$2; ZEILE()-ZEILE($A$1)-1; 0)+BEREICH.VERSCHIEBEN($C$2; ZEILE()-ZEILE($A$1)-1; 0))/2
Maximum
=BEREICH.VERSCHIEBEN($B$2; ZEILE()-ZEILE($A$1)-1; 0)
Thank you for your understanding and patience
- JuttaWERUFeb 01, 2023Copper ContributorThanks for providing me with the German translation!
But I still have the problem that the formula refers to the 1st min and max.