Forum Discussion
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 your support,
Jutta
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
19 Replies
You may use OFFSET for the gradient and icons
min: =OFFSET($C$2, ROW()-ROW($A$1)-1, 0) midpoint: =(OFFSET($B$2, ROW()-ROW($A$1)-1, 0)+OFFSET($C$2, ROW()-ROW($A$1)-1, 0))/2 max: =OFFSET($B$2, ROW()-ROW($A$1)-1, 0)Thanks for your proposal.
It works when downloading your excel , but not if I copy and past to my excel: => Invalid formula
Perhaps you shall use semicolons in formula instead of commas. Could you share your file?
So your requirement is to create 1000s of conditional format ranges, one per row?
Rather than creating thousands of conditional formats, you might consider creating two formats each applied to the entire range.
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)- 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.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.
I am not sure I fully understand your objectives, so let's start with a simple 'solution'.
Use Format Painter to select the conditional format, then apply it to each target range separately.
Any of the limits may be adjusted using the dialogue boxes you show, as can the colours.
