Forum Discussion
Kjell Rilbe
Nov 15, 2017Copper Contributor
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...
- Jan 27, 2022
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.
PeterBartholomew1
Aug 23, 2021Silver Contributor
Interesting that you apply CF to entire sheets (entire rows/columns also works). In my experience that is a very unusual practice but one that appears to work in that it does not expand the used range or appear particularly resource intensive. I fully agree that it is a pain that this antiquated bit of functionality will not accept a named range as the range to which the formatting is applied but, instead, follows its own rules to generate collections of individual cells.
Because of the way in which CF works, it appears to be perfectly normal for the .AppliesTo range to look like something the dog has chewed. Constant repair seems to be called for!
You won't have encountered it yet, but the inability of CF to recognise dynamic array ranges is also a severe limitation. I think Microsoft is aware that conditional formatting falls well short of expectations, but the code base appears to be ancient and will require a huge amount of effort to rectify its shortcomings.
B_Famous_T
Aug 30, 2021Copper Contributor
I don't apply CF to entire sheets. Only ranges within the sheet.
- PeterBartholomew1Sep 01, 2021Silver ContributorThat would be my preferred option, too. I found it interesting that, while large ranges of conditionally formatting affect the used range, entire CF columns or sheets do not change it.