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 figured it out. My example is column-based formatting and when inserting a new column the static cell formula and conditional formatting ($I$10) becomes $J$10 which I do not want.
I found that making the formulas and CF as I$10 then inserting a new column and dragging it over (becomes J$10) then deleting my column will re-align the J$10 to become the original I$10. This applies to the CF as well.
Took a little longer than I hoped to get this working and it was a pain. Excel should respect static cell inputs or make absolute static cell inputs (e.g. $$I$$10) so it is respected no matter what happens to the sheet elsewhere.
I have a situation where I have one column formatted and I am just cutting and pasting cells from the other columns to a different line. No column or row insertion, simple cut/paste. The format only applies to column F. So I am highlighting A-D of one row, say 15, and I cut, then past the values onto row 10.
I can tell right away that it's screwing things up, as the paste triggers the CF to turn column F green if it matches column A.
It somehow still screws up my conditional formatting that is only on column F.
Explain that one!
kennnels - instead of "cutting" or even dragging the data to a new position, I would rather copy and paste as value(V).
If you think about it, you are physically moving the reference of the formula in F15, to a new position. Therefor the original formula of F15 will autocorrect it self and will now reference the data in row 10.
As an example, I created a table A2:B9. I use a formula (=A2*$A$1) throughout a entire column B, to reference a value, originally positioned in A1. If I "CUT" A1 and paste the data in A20, the formula of the entire column will autocorrect as (=A2*$A$20) and all values will still be calculated correctly. Even if you move the entire table to column Z, the formula autocorrect to (=Z2*$Z$20)
In many cases, this is what you would want, but in your situation, you should rather just copy and paste as value(V).
PS: I am no expert and stand to be corrected...
