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.
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.
Hi, same problem here. Working with 3 x 30'000 datasets in one sheet we have to insert singular sets of 8 rows (example K-R) per line v downwards. Excel always presses preference >rightwards.
Whole blocks of columns destroyed
Tried to re-edit the dialog box into permanent "downwards" option, no chance.
If you'd find any solution, please get in touch.
I suspect the problem is that conditional formatting is an antiquated piece of junk that will take a fortune to sort out. You are more likely to get added 'bells and whistles' than a substantive rewrite.
The main challenge now is to create something that works well with dynamic arrays (I use 365 and my solutions comprise nothing but names and dynamic arrays). If I create a dynamic array of formats (which may cover header rows, numeric results, white space between output blocks), I want it to apply to the range used by the dynamic array, whatever that happens to be at the time.
For a year now I've been redoing the conditional formatting every couple of days. Yes, It's crazy this hasn't been addressed. The same thing happens with the Data Validation, inserting lines stops that from working and I have to manually set it again.
It feels like we're using Excel in the year 2000, a spreadsheet for the new millennium! The new AI stuff makes bad design like this seem so out of place in 2023.
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- 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?
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 FunctionNext, 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 !
- 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.
