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.
stacyalabardo Adding or removing rows will not affect a reference like $E:$E as that points to all rows. Try a regular SUM formula like that in a cell and start adding rows. Watch the formula. CF formulas work exactly the same.
JKPieterseThank you for responding! Unfortunately, that hasn't been the case with my version of Excel for Mac, Version 16.76 (23081101) on my 13-inch, 2020, 2.3 GHz Quad-Core Intel Core i7 MacBook Pro with 16 GB of RAM. This is happening in a shared file across a network. Does that make a difference given that everyone probably doesn't have the exact same computer as me? Also, some of those folks use the browser version of Excel. Could that be the reason? It happens mostly when a row is deleted. Your expertise would be helpful. TIA
- I forgot to mention, all my conditional formatting pertains to columns.
- What -precisely- happens? Is the CF rule not extended to include the new row by any chance? You can prevent that from happening by first copying a row and then right-clicking and choosing insert copied row.
- Yes, the CF is maintained when a row is copied and pasted as you describe.
The issue comes when a row is deleted. The CF range then changes from $E:$E to something like $E$1:$E$20,$E$21:$E$10k (10k=whatever the max row number is in Excel) and it becomes unwieldy to maintain.
I will also find CF rules duplicated when I check the CF for the Worksheet.
Could this be caused by the browser version of Excel of copying/pasting a row?
FYI, I have 42 conditional format rules.