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
Use conditional formatting to highlight information
Copy and paste conditional formatting
If you want to apply an existing formatting style to new or other data on your worksheet, you can use Format Painter to copy the conditional formatting to that data.
Click the cell that has the conditional formatting that you want to copy.
Click Home > Format Painter.
The pointer changes to a paintbrush.
Tip: You can double-click Format Painter if you want to keep using the paintbrush to paste the conditional formatting in other cells.
To paste the conditional formatting, drag the paintbrush across the cells or ranges of cells you want to format.
To stop using the paintbrush, press Esc.
Note: If you’ve used a formula in the rule that applies the conditional formatting, you might have to adjust any cell references in the formula after pasting the conditional format. For more information, see Switch between relative, absolute, and mixed references.
Hope I could help you with these information / links.
NikolinoDE
I know I don't know anything (Socrates)
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 :).