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.
gahh... I've been all over the internet looking for an answer to this. Even when I try this, it takes the formatting from the cell above. So I need Cell N# to be green if it is equal to the corresponding cell in column E. So kinda like this, =$E$11=$N$11 and formatted to Green color and Green text. And then one for > and one for < in Red. But when I add a row, whether it be in the middle, or bottom, it seems to pull the formatting from the previous row. Making it look like =$E$11=$N$11:$N$12 and same for the <> ones as well. Its telling that cell to equal the line above. Which in turn will always make it Red cause it is not going to equal the line above most times. And it won't allow me to change it without messing up all the conditional formatting for the lines below it. This is so frustrating.
- I confirm Ruan88's solution :
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).
