Stopping a Conditional Formatting Rule from Breaking into Smaller Ranges

@Roy
Perhaps this is what you meant. Instead of deleting rows 3 and 4 entirely, if I right-click selected cells A3:A4 and pick Delete... with "Shift cells left" then Applies To range becomes =$A$1:$A$2,$A$5:$A$1048576

@Roy
I'm using Excel 365 version 2211. When I repeat your scenario, the conditional format Applies To range remains =$A:$A

@Fernando

I'm on 2212. I created an A:A conditional format range, then deleted rows 3 and 4. Applies to range then was Balkanized. As has always been, so nothing new here.

Good luck though.

@ J. Woolley - I don't like macros or workbooks with macros, avoid them like the plague, but thanks. :)
@ Roy - I don't usually delete rows, but I've just tried it now and both my CFs stayed the same, they were not Balkanized, so maybe something's improved recently with Excel? ¯\_(ツ)_/¯

@Fernando & Roy
Please review my comment dated 2021-08-14 10:11:28 below.
My Excel Toolbox includes macros to backup and restore the active sheet’s conditional formatting using named ranges that auto-adjust to row/column changes. Run CFBackup before your conditional formatting rules are broken. If they later become broken, run CFRestore.
The following dynamic array function is also available in My Excel Toolbox:
=ListFormatConditions([AllSheets],[SkipHeader])
This function returns Applies To Range, Type, and Stop If True for each CF.
You can use the SpillArray function to simulate a dynamic array in older versions of Excel:
=SpillArray(ListFormatConditions(...))
See https://sites.google.com/view/MyExcelToolbox/
See https://excelribbon.tips.net/T006253_Copying_Conditional_Formatting.html

@Fernando:

The A:A trick is nice to set things up, but fails with the first row deletion. Excel calculates an entirely new set of addresses to rangify the other million rows, and then that Balkan-ized mess becomes the start of your new mess.

And "etc."

I did a bit more digging and found something that works for me when Inserting blank rows.
I followed the advice in Superuser: https://superuser.com/a/1172256

Essentially, I have two CFs in a spreadsheet: one I got around it by simply using $D:$D in the Applies To field; the other one I couldn't do that as I was comparing values with the previous row but by using INDIRECT I could now use $A:$A in the Applies To field for that one and this stops Excel from being silly and breaking up the CFs when you insert a row.

These are the two CFs I'm using, in case they're useful to others:

1. this is used to highlight a cell that has a lower value than the one in the previous row - I use this to ensure I'm not typing a date out of sequence:
=INDIRECT("A"&ROW())<INDIRECT("A"&ROW()-1)

2. this one I use to ensure that if two cells have values, they need to be the same (can you tell by now I'm using a spreadsheet to balance accounts? :))
=AND(D1>0,B1>0,B1<>D1)
This one, as is, will (incorrectly) highlight the column header because it will fail - I could have complicated the formula and excluded that but I decided against so as not to overcomplicate things and potentially make excel slower, even though I use a decent computer.

Hope this can be useful to others.

I got to your article trying to find out how to stop that behaviour when just Inserting a new row. Your title is deceptive in that it does only cover paste, not Inserts... Do you know of a way to stop Excel breaking up those formulas when inserting new rows?

Geez, another sort of small point:

The "Applies To Range" box acts like the "Refers to" box in the Named Range manager (not to mention, sadly, the F2-Edit formula editor) in that its default for typing is basically to act like you are mousing or arrowing about in the sheet to pick cell references. So while sometimes, highlighting a comma and typing a colon gives you a colon, it far more often might give you some cell reference. So "$A$1:$C$7", highlighting and typing a new colon for the old one, you might see "$A$1:$C$7" inserted instead of the colon you typed giving you "$A$1$A$1:$C$7$C$7"... but before you make Excel use the result as best it can, including not at all, you DO have Undo available to return the string to what it was before for a new try. Like with the NR manager, you can press F2 to be in a pure typing mode and it works nicely.

Now and then, it works right off. No idea what's different, but since it does, one can be lulled and forget it normally needs F2 first.

"Small point"... a person might not try using the trick if it goes badly due to this, so maybe not so small after all.

Two small items I meant to mention but got lost while typing:

1. The range fix I mentioned using the changing of commas to colons: if you make a change and click "Apply" you can see the change immediately. No need to exit and come back. The latter's more of a "you know you fixed it and some other time you might come back for whatever reason and notice the change" kind of thing. Also, another thought is that it is not part of the Undo stack so you can't use that to restore what you saw when you began fixing.

2. By ' "All, Merge Conditional Formats" option ' in the Tip, Mr. Wyatt of course means "It will add the rules that come with the pasted cells so you ARE going to be stuck with extra rules that you have to clean up, but at least the range pasted onto did not Balkanize." I read what was written as the idea that identical CF's would be merged into the existing one, not inserted, and non-identical ones would be. I'm reasonable with reading for comprehension, mostly, so figure others might read it the same way and not realize they are leaving funny, at best, or nasty, at worst, extra rules inserted as Excel sees fit. Remembering that a fair amount of related CF stuff seems broken/dumb/barely implemented, "as Excel sees fit" doesn't inspire the confidence that Tom Brady does.

Those insertions can carry problems, not just interfere. For instance, in the paste range you need "Stop If True" not checked. In the copy range, you had something a bit different going on and needed it checked. Now you have a random insertion that is checked in a place that works only if not checked. Subtle enough I wager it would often be thought of AFTER hair-pulling has commenced, not before. That's nasty.

In the middle would be things like the copy range used a color used for a different purpose in the paste range. How long would it take before you realized the color that is inappropriate is due to this and not your rule using it failing, or your whole balanced sequence failing? Not necessarily harmful, like the "Stop If True" thing, but it could be very vexing.

So Excel's clumsy ham-fisted working using this is very much not a panacea. (Sigh...)

@Peter & Roy
Re. "repair macro," please review my comment dated 2021-08-14 10:11:28 below.

Thanks Roy, I like your comments following
<A trick that is handy for ranges that CF presents in this manner: "=$A$2:$C$10,$A$12:$C$16" >

I was looking for a way to repair the ranges in code, now I have it, and very elegantly. Thanks. I take your warning: I would have to careful about cases where the applies-to range is meant to contain discreet, non-overlapping areas.

We have wondered why Excel does not do this automatically on inserting a row. The strange thing is that Excel does expand the range when you insert a column. It seems that all formats conditional or otherwise are copied into the new column. Maybe it has something to do with casting the formula in terms of the top row of the applies-to range.

When I cut and paste-insert from within the formatted area, there is no disruption to the applies-to ranges. But as you say, when I copy & paste formatting from one row to an adjacent row with identical CFs, (without inserting) the ranges are disrupted and the number of CFs doubles.

I'll think about a repair macro a bit more. Maybe it could be linked to worksheet_change()

@Peter: The Named Range thing will not remove the splitting (duplication but with Balkanized ranges) of the rules.

They tend to stay in batches though, so changing the range of the first of identical-except-for-range rules, then deleting the others works for me.

What's really messy is the insertion of rows that carry their own, different, usually, but even when identical (Copy and Pasted out, then the new location Cut and Inserted, so... identical), rules. That's the true nightmare and using Named Ranges doesn't remove those extra rules. Unfortunately, care with pasting choices doesn't seem to help there either. "Merge" seems to mean "Keep everything from both groups."

So you'd think Copying and Pasting a row over a CF range component (rather than insert) would "Keep everything from both groups" with the Pasted row's contribution being... zero... so the rules and range would stay the same, or at worst Balkanize, but Excel sees that as somehow just different and leaves the Pasted cells out of the CF rules and range. (Sigh...) Insertion would have added an identical rule for the Pasted cells, but enlarged the original range to include them so one could just delete the fairly clear (sort of) extra rule/s, but Paste is subtractive.

I should say though, that I never really use anything too complicated. I have read descriptions of CF-ing that people have in place which describe very long sets of rules. Any time one takes the "change the first one to be all you need, then delete the otherwise identical ones from the Balkanizing" approach, there is a huge risk of becoming careless and deleting just one too many... And also the chance of deleting exactly what one planned to, but not noticing that a subtle shift had taken place and that one included one or some of a second set of Balkanized rules when deleting... These risks would become FAR more likely to be things that happen rather than risks if one had a long set of somewhat similar rules. The simple versions I use carry the risk, but since the similarities are often not overly pronounced for my uses, the overall risk is small for me, mostly the get on a roll and do one too many concern. So I don't get on a roll. But lots of folks do some pretty involved things and the second concern would loom large for them.

It's bizarre, really. You'd think we would be complaining that Excel is "too smart." That it does things like see a set of ranges like "A1:A3,A4,A6,A5:A8" and "repair" them into "A1:A8"... and that that doesn't allow some arcane effect we think we could achieve. That it does things like let us use a NR to set a Print Area, but immediately changes it to an absolute range that is not dynamic as the NR changes, but because it has some smart reason, some bad thing that would occur, not that it is just too dumb to be able to save such a thing. (By the way, it WILL preserve a Table reference and it is dynamic. Print Area, that is, not CF.)

A trick that is handy for ranges that CF presents in this manner: "=$A$2:$C$10,$A$12:$C$16" can be fixed by changing the comma between the two range parts to a colon and saving the change. When you bring it back up so Excel has a reason to re-present the range, it will be "=$A$2:$C$16" (good, if you wanted A11:C11 back in it). A lot harder to use though, if one's range mixes range parts that you do not want "healed" like that.

(It uses Excel's interpretation of a range of the form "A1:A5:D2:C1:C3" as a range from the leftmost column in it to the rightmost column in it mated to the top row in it to the bottom row in it, so columns A through D and rows 1 through 5, or A1:D5. That means the more complicated your string, the more likely you'll end up with an incorrect result if just blithely replacing commas with colons. So you do have to watch out a bit, but simple ones aer very straightforward. Mixed ones... not so much. Could be an interesting exercise to write a formula for. Copy out the complicated range, let the formula group elements and reduce the range, infilling in the probably desired manner. I bet no one would ever use it, but still... interesting.)

I have had similar issues with vlookup when I cut and paste. Cutting keeps an address absolute instead of relational. What works better for me is to copy a row, insert the copied row where I want it, then delete the original copied row. More steps but things don't fall apart on me.

The tough part is; I have to train others to do this so they don't mess up their spreadsheets. Fortunately, we don't share worksheets.

Hi Again. I did the same again and there was no change in the applies-to ranges. Not sure what happened the first time.

I did another test - cutting a row from below row 200 and inserting at row 175. This was really weird.
It seems that some format conditions had their applies-to ranges preserved/updated like $G$3:$G$200,$Y$3:$Z$200 (formula =G3) (applies to two discrete ranges) but the majority of applies-to ranges were split like $N$3:$N$174,$N$176:$N$200 (formula =$AS3). There is a common feature of the applies-to ranges that did not get split and that is that none of the corresponding formulae had an absolute address. However, there was one without absolute addresses that was split.

Having done that I copied the formatting from row 174 to update its formats. As has been discussed by others, all the conditions were duplicated just for row 175. The applies-to ranges like $G$3:$G$200,$Y$3:$Z$200 were balkanised in the same way as the others: $G$3:$G$174,$Y$3:$Z$174,$Y$176:$Z$200,$G$176:$G$200 (formula =G3), and separately $G$175,$Y$175:$Z$175 (formula =G175). Notice that the order G,Y is not preserved, so repairing them is messy.

The good news is that I now have a list that I can use to rebuild the format conditions etc should it get to that. And I think it will be necessary.

Regarding named ranges, I can see how that would fix half of the after-insertion conditional formats, but does it delete the spurious, inserted conditional formats and formulae?

Build 2107 also.

VERY interestingly, I experimented just now in order to respond with an idea for Peter's post and find I NOW have NONE of the usual problems.

Fascinating. Since I haven't had any upgrade in Excel (or Office) since my last comment.

Now when I insert a row, or insert a row cut either from the Range of the CF or from outside its range, any of the three, the range is modified as one would hope it to be.

A1:A10 becomes A1:A11. Simple as that. Balkanization isn't occurring unless I create an actual problem myself. (For instance, Cutting row 6 from the block and inserting it as row 200. Then I get "A1:A9,A200" which one would expect. Cut the row from there and insert it anywhere in the original range and it returns to "A1:A10".

When I get time, I will certainly experiment further. But for now... very interesting... Build 2107, by the way.

Thanks for the ideas. My conditional formats do get into a mess and randomly stop working.

I modified some code I found to list the conditions in use in one of my databases. This is a bit handier than the dialog. There are now 18 different conditions applying to one or more columns, after I tidied them up. They are all based on formulas. The data ranges from rows 3 to (at present) 185, so I formatted down to row 200 to allow for extra data coming in. The data is not formatted as an array.

I was very happy with the result until I cut and past-inserted a row of data within the database (say row 130 to row 110). After that all the Applies_to ranges were updated from rows 3 to 200 to rows 3 to 199. I expect to cut & paste-insert at least another 40 times this year. Does that mean the Applies_to ranges will shrink to row 160 or so, leaving the remaining rows with no conditional formats?

The tip about using "Paste > Paste Special > All merging conditional formatting" will save me a lot of periodic cleaning up of the Conditional Formattings that I use extensively. That command is a Godsend that I had overlooked. It seems that should be the default for Ctrl-V.

Unfortunately, since that is NOT the default for Ctrl-V, it means a lot more mouse clicks every time you need to paste a row or column. Fortunately, "Paste and Merge Conditional Formatting" is an option for the Quick Access Toolbar (QAT). So now I just have to train myself to avoid the highly ingrained Ctrl-V and use that newly added tool on my QAT.

I really appreciate the feedback from all of you. I'm not sure which method would work best with my particular layout but I plan on experimenting with these suggested solutions and figuring it out.

It looks like this breaking conditional formatting is a pain to a lot of EXCEL users. Here is my macro version for repairing it:

-----------------------------------------------------------------------------------------------
Sub CondForm()
'
' Macro
' Conditional formatting date and comment column in Inventory
' Feel free to use no copyright
'
Dim Last_Row As Integer
Range("AG2").Select
Last_Row = Range("A2").End(xlDown).Row
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1:A" & Last_Row - 2).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("AN2").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1:A" & Last_Row - 2).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
----------------------------------------------------------------------------------

I added this to my personal menu (with all my other useful macros)
Feel free to use or make it more efficient for the column as I only have conditional formatting on those 2 columns.

By the way, one part of the Tip mentions pasting special, formulas.

I have had users in the past bothered that they have to make a choice between pasting values and pasting formulas. As in, say A1:C1 have the following entries:

8
=AA43-A1
29.4

They want to paste VALUES from A1 and C1, but paste FORMULAS from B1 when pasting into, say, H1:J1. So they are bothered by the choices given and having to do 2-3 pastes, depending upon how they organize them.

However, Paste|Special|Formulas will paste all three of the entries above exactly as they wish, so they could do that simple one paste operation. P|S|F pastes values ("constants" to Excel, but for some unknown reason, it uses "values" in the P|S dialog box instead of "constants" when referring to P|S|V and just "formulas" instead of "formulas and constants" when referring to P|S|F) if values are present, and formulas if formulas are present. So there is no need to do each kind of desired paste separately.

One solution, if macros may be used is to define the range as a Named Range, then to write a macro that resets the Conditional Format’s range to the Named Range.

One can then activate the macro in any number of ways including taking over the shortcut for row insertion. If an “On Insertion” command exists in VBA, that would be ideal.

If one took this approach for all one’s Conditional Format ranges, one would just add a line to the macro laid for the first one. Of course, one would need to remove them, or capture failure-to-find errors, if a Conditional Format is removed.

Of course, no matter that one sets the Conditional Format range via Named Range, it will instantly be converted by Excel to a literal, absolute range. But it has still been reset to whatever the Named Range is. The thing that makes it work is that the Named Range functionality WILL absorb the insertion and adjust to the new address for its range. So each time the macro activates, it gives the Conditional Format “Applies to” the adjusted Named Range so the “Applies to” range changes from the Balkanized range the insertion caused to the proper range including the inserted row.

Such a macro could also be triggered in an “On Open” or “Before Close” (or “Before Save”) set of routines to ensure all is as expected before or after using the file. Why not? It’s already a macro file, so…

I don’t use the macro aspect, but do use the Named Range restoring “Applies to” ranges for CF’s I find myself ruining due to Balkanization. If macros are not permitted for Brian’s situation, that would work. It eases the restoration and aids accuracy of it, so it’s pretty useful. Limited basis of course, due to the by-hand nature of doing it thus, but still nice.

Another way would be to convert the range of cells into a Table BEFORE establishing the Conditional Formatting. I find, in limited experiments, that either inserting rows (just a new row, or a copied to the clipboard row) or inserting cells across a row of the Table sees the Table able to expand with the CF applying as expected. The range in the CF is changed to the new dimensions. Seems the Table functionality includes following up on that kind of thing.

I did NOT experiment with inserting rows that bring their own CF into a Table.

An interesting and obnoxious note for Tables is that I found setting a CF on a range, then converting it to a Table, caused all kinds of trouble in the CF "Applies to" regard. For simple CF's, that's probably easy to sort out and then things would be fine, adjusting automatically as above (probably... did not experiment on that). But complex ones... the world's open on that. One rule, one five row by four column "Applies to" converted to a Table (still one "Applies to" after doing so) but inserting a row caused the CF rule to break into four "Applies to" regions, the last of which was correct for the range specified, but set in a way that caused changes in a row of that bit if the row ABOVE it met the CF conditions, not if the row itself did. Still fairly easily sorted, but if you have 3-4-5 CF rules applying to parts and/or the whole of the range when you inserted a row, you could end up with a massive nightmare. Again though, I did NOT push that set of experiments.

Hence saying to convert to a Table FIRST, then apply CF rules.

I've run into this issue before and the way that works best for me is to keep a blank row with the conditional formatting in the header area of my spreadsheet to be used to copy to the new data I paste in. Due to the type of data I have, I've gotten into the habit of leaving blank rows at the top of my spreadsheet and always pasting the header info at row 5 and the data follows at row 6. Row 1 to 3 is where I put other header/demographic type info, totals, subtotals, etc. Row 4 is left blank and is where I set up the conditional formatting. For each new month, I use the prior month's file as the starting point. (Works better for me than a template file as each month I may need to add columns for new data, etc.). I clear the prior month's data, save the file as the new month, paste in the new month's data at row 6, then copy the formatting from row 3 to row 6 through the last row.

Thanks always for the great tips!

Like many, I’ve been frustrated when my carefully crafted conditional formatting becomes bifurcated after innocently performing a copy/paste or some other such sin. And Excel’s miserable Conditional Formatting Rules Manager makes it a chore to fix the mangled rules. So My Excel Toolbox includes macros to backup and restore the active sheet’s conditional formatting using named ranges that auto-adjust to row/column changes.
See https://sites.google.com/view/MyExcelToolbox/
There’s too much VBA code to post in this comment, but here’s a synopsis of CFBackup, a macro to backup the active sheet’s conditional formatting (CF):
  + Create a new backup sheet
  + Define a named range with Workbook scope relating the backup sheet to ActiveSheet
    - A named range will auto-adjust if ActiveSheet is renamed
  + Add a defined name with ActiveSheet scope to register information about the backup
  + Define a named range with ActiveSheet scope for each CF rule's AppliesTo range
    - A named range will auto-adjust if rows or columns are inserted or removed
  + For each CF rule:
    - Locate the first cell in the rule's AppliesTo range
    - If the first cell is merged, copy it to a temporary cell before it is unmerged
    - Copy the first cell's CF rule(s) to the backup sheet
    - Isolate one unique rule from the CF rule(s) copied to the backup sheet
    - Record the rule's defined name, range formulas, etc., on the backup sheet
  + Protect the backup sheet
Here’s a synopsis of CFRestore, a macro to restore the active sheet’s conditional formatting (CF):
  + Delete all CF rules on ActiveSheet
  + For each unique CF rule on the backup sheet:
    - Copy the CF rule to a temporary cell on ActiveSheet
    - Modify the temporary cell's AppliesTo range to match the CF rule's named range (auto-adjusted)
    - Delete the temporary cell
  + Protect the backup sheet (if appropriate)
Run CFBackup before your conditional formatting rules are broken. If they later become broken, run CFRestore.