Keep conditional formatting range when inserting/deleting cells/rows/columns?

[Kjell Rilbe]

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 that have content.

But then I need to insert or remove data, and that mucks up the range. It seems to copy the entry to the range above, the inserted range, and the range below. I want it to keep the range and entries as they are when I edit the sheet's data.

How?

I usually enter the range as e.g. $1:$1048576 or $A:$XFD.

[#2]

If you first copy an entire row before inserting a new one (Insert copied row) I expect this won't happen. Otherwise, I feel your pain. The CF rules manager is one of the most unusable interfaces Excel has.

[#3]

That doesn't work in Excel 2016.  I'm wondering if there is a way to set the "Applies to" field to a named range vs. $A:$A.

[#4]

Not that I know of, Excel manages that list and I expect inserting a row will override whatever range name you put there.

[#5]

Hi guys

Adding a name range should work allowing you to add or delete rows without affecting your formulas.

Just make sure that any formula refers to the name range you have specified.

Cheers
Damien

[#6]

This doesn't work - when a named range is referred to, it immediately evaluates it, so using a named range is equivalent to entering the definition of said range.

One thing I've found in my spreadsheet is that inserting a row only affects one conditional formatting entry (probably because this is the only one which uses a formula, one which refers to other surrounding cells). Nonetheless this is annoying as the 'Manage Rules' dialog is difficult enough to use (you can't even resize?!) without rules being spawned with every spreadsheet edit.

[#7]

This is the most frustrating issue with Excel that I'm having. We use Excel 365 for work and keep tabs on the current progress of repairs. We constantly need to insert, delete, and rearrange rows. Every time we do, we have to fix the conditional formatting range. Please help if anyone knows how to get around this without manual intervention each and every time.

[#8]

I've also been frustrated with this for a long time. I wonder if anyone can answer this: Is it possible to write a macro that would delete the existing condition format and create a new one with the correct range?

[#9]

Of course there is 🙂
A work-around may be to remove all CF but the first row (assuming this is a table) and then copy/paste special formatting that first row on top of all other rows.

[#10]

I have been experimenting with a rough workaround for this problem that applies if you only need to insert new rows OR columns but not both

If you set the conditional format range as entire rows eg ($5:$50), you can then insert new rows within this range to automatically expand it to ($5:$51) without it breaking into pieces. But:
- It only works for conditional formats based on a formula; however, you can mimic (for example) traffic light icons by using character "l" in the Wingdings font and then applying a custom number format of l;l;l;@ on the required range. This forces the values 1, 0 and -1 to all display a filled circle, with the ability to then use conditional formatting to change the colour based on the underlying number value
- You will likely need to include an AND statement in every conditional format formula to stop it from formatting cells in unwanted columns outside your desired range. eg = AND(A$1="Y") and put "Y" in row 1 for all columns that require the format
- Select "Stop if true" on each formula to help improve performance
- The approach will only work on either Rows OR Columns. For example switch to a range of (say) $B:$Z if you wish to add columns within this range

[#11]

@Kjell Rilbe 

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.

[#12]

I don't apply CF to entire sheets. Only ranges within the sheet.

@PeterBartholomew 

[#13]

That 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.

[#14]

@B_Famous_T 

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.

[#15]

@AdamS_PL 

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 !

[#16]

You are right.
Seems like I didn't test this solution good enough.
No more ideas how to overcome this.

[#17]

Solution to this it anchor but both column and row value.

If you want whole column Instead of doing $A:$A do=$A$1:$A$100(pick range more realistic to your needs)

Adding new row will change from $A$1:$A$100 to $A$1:$A$101 instead of excluding it

If you to do multiple columns just from $A:$C just do $A1:$C100 instead.

Test it out your self see if you can find any bugs i might have missed. But solve the core problem.

[#18 · Accepted solution]

@Kjell Rilbe 

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.

[#19]

@Kjell Rilbe 

Microsoft, couldn't you just add an optional fixed flag to the range option? Everyone is sick of this issue, and the only workaround I've ever found is to use event to re-establish the range, which his just daft.

[#20]

Not so good if you need to insert data into a range rather than just extending the range, or have a I misunderstood?