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.
The simplest workaround is to create own function which will return required range.
In my case it was whole column, but you can make more complex versions of it, for example specific range like "B1:B15".
Function GetColumn(columnName As String)
GetColumn = ActiveSheet.Range(columnName + ":" + columnName)
End Function
Next, in CFR Manager you are going to indicate required range by using the formula:
Now, you can copy, insert, delete rows, but excel won't crumble it.
Thank for this idea !
However, I tried it, and when I click "apply" on the CF window manager, my initial range is still displayed, as if my entry was not valid.
Before
After
And the formula is working..
Any idea what I got wrong ?
I tried also to use "INDIRECT" function, but excel just evaluated the range when applied.
Thank you !
- You are right.
Seems like I didn't test this solution good enough.
No more ideas how to overcome this.