Forum Discussion
Keep conditional formatting range when inserting/deleting cells/rows/columns?
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.
I don't call myself an "Excel Expert" by any means, but I like to struggle with things until I find some sort of simple solution.
In my case, I have 3 x CF rules applied to 2 different columns in a table, as example: "=$Q$2:$Q$185"...
As soon as I insert a row, it splits the "Applied to" range, and add a set of 3 new rules. But if I add a row to the very bottom of this table and apply my "custom sort" instruction, the range gets extended to "=$Q$2:$Q$186"...
This is what worked for me and hope someone can also make use thereof.
1 add data to the bottom of the range
2 resort the range
Note that moving the row manually (using shift+drag) from the bottom to the middle of the range will break Conditionnal Formatting (unlike sorting the range).