Forum Discussion
Conditional Formatting applying to old columns when new columns are copied and inserted.
I have columns that have conditional formatting applied using a CONCATENATE formula.
When I copy and paste the new columns, the formula still uses the old columns and doesn't pick up the new ones. Please can someone help? I have attached a sample file.
For example, columns AK & AL are picking up columns AN & AO when I copy the three columns.
20 Replies
See if this works better for you. I replaced all rules with a set of 6 rules for the entire range.
- Hi Hans,
Please can you explain the logic around this?
Will it only work if the column A is locked?The formula refers to A3, not to $A3, so Excel will dynamically adjust it in other columns.
The formula first uses MOD(COLUMN(...),3) to check whether the cell is in a column that is to be formatted conditionally.
If so, it uses OFFSET in combination with MOD(COLUMN(...),3) to determine the two cells to be concatenated. For a cell in column A (or D or G etc.), it is the cell itself and the cell to the right.
For a cell in column B (or E or H etc.) is the cell itself and the cell to the left.
This makes the formula much more dynamic.
