6
2
I have this conditional formatting set
but when I move/delete/copy/cut/paste/insert a cell/row/column my conditional formatting becomes like this
The rules have duplicated themselves, the "Applies to" now is broken up and from the little bit you can see one of my blue rules has an altered Rule.
Sometimes I make mistakes when making an Excel spreadsheet requiring me to move and or delete stuff from a single cell to entire rows. Why does Excel think it's necessary to butcher my conditional formatting ? Especially since by creating these duplicate rules it gives me more work to do if I need to refine them later on which is what I use conditional formatting to avoid doing i.e. more work
Memor-X
Posted 2016-08-14T23:49:53.813
Reputation: 505
FWIW, I've just did some basic testing in my Excel 2016, and no matter how I copy/pasted, inserted, or deleted, my range stayed "=$A:$D", except when I inserted or deleted a column within my range, and then it just expanded or contracted my range as expected. As with most weird app behaviour, I have to ask if you have all the latest service packs and Office updates installed? – DarkMoon – 2016-08-14T23:59:03.550
@DarkMoon well my PC is Windows 7 SP1 and Windows Update does say it's all up to date (Excel points me to Windows Update when i go File > Help > Check for Updates) so i would assume it's all up to date. as i have tagged 2010 it could be sometime between after 2010 and 2016 versions of Office this was fixed up as being a bug – Memor-X – 2016-08-15T00:04:03.893
Yeah, I was more pointing out that that behaviour isn't happening for me, so it's not "by design". :-P Hence the question about updates. When you're in the Windows Update screen, there should be an option to enable "updates for other Microsoft products", which is off by default in Win7; have you enabled that? If not, you won't see Office updates in Windows Update. One other thought; can you reproduce this behaviour in a new workbook? Might be something wrong within the file rather than Excel itself? – DarkMoon – 2016-08-15T00:09:51.330
What's in lines 73-74? What happens if you delete lines 73-74 before performing the operations that cause the rules to change? – techraf – 2016-08-15T00:16:30.057
@DarkMoon "Give me updates for Microsoft products and check for new option Microsoft software when i update windows" is on, this is a work PC so i assume it was enabled. as for new workbook i assume you mean new file and yes i tried that, aside from that the file the screenshots was a new file created only a couple of days ago i started a new book, created a simple single rule and did some cutting, pasting, moving, deleting it did the same thing (albeit different cells refences) – Memor-X – 2016-08-15T00:19:49.760
@techraf well in the new book i started ^ when the same thing happened but with different cell values, the rule which represented what the second blue rule in my screenshots shows (the one which applied to
$D$74) when i deleted the row the rule just disappeared. i would assume it would be the same for the original speadsheet i took screenshots (i undid the changes until the rules were restored and manually deleted data inside cells and retyped to avoid the butchering) – Memor-X – 2016-08-15T00:23:40.1331
Possible duplicate of Excel conditional formatting fragmentation
– Fabian Schmied – 2019-01-03T08:22:58.997