Forum Discussion
Keep conditional formatting range when inserting/deleting cells/rows/columns?
Hi,
I sometimes use conditional formatting. For each entry, there's a cell range that it applies to. Often I need it to be used on the entire sheet, or at least a large range of it, i.e. all rows that have content.
But then I need to insert or remove data, and that mucks up the range. It seems to copy the entry to the range above, the inserted range, and the range below. I want it to keep the range and entries as they are when I edit the sheet's data.
How?
I usually enter the range as e.g. $1:$1048576 or $A:$XFD.
Was having the same issue and so my search led to this forum, searched everywhere else and couldn't find the answer, played around with it and finally figured it out. It's actually a pretty simple solution.
Instead of Inserting a column or copying and inserting a column, all you have to do is select the column cells that you want to extend, then at the bottom of the cursor where the plus sign is, click and drag to the right as many columns as needed.
The CF range extended to the last column without creating any extra conditions or messing up the original range.
41 Replies
- If you first copy an entire row before inserting a new one (Insert copied row) I expect this won't happen. Otherwise, I feel your pain. The CF rules manager is one of the most unusable interfaces Excel has.
That doesn't work in Excel 2016. I'm wondering if there is a way to set the "Applies to" field to a named range vs. $A:$A.
- Not that I know of, Excel manages that list and I expect inserting a row will override whatever range name you put there.
