I set a rule range to =$A:$A but when I add or update rows the range gets a mess: the rule gets duplicated to single cells and those cells are "removed" from the main rule, so I get something like:
=$A43=$A:$A42;$A44:$A111111Why so and how can I fix this??
I tried with a namedrange and INDIRECT, but they don't work
This is a well-known problem with Excel and complained about for decades. You can add your voice to the hue and cry at, well, looks like they're dumping all the old complaints and doing something, gosh, new... You can read this for (some little) detail:
https://support.microsoft.com/en-us/office/uservoice-pages-430e1a78-e016-472a-a10f-dc2a3df3450a
So, after the hue and cry portion, what can you do to make it better?
(Bear in mind, you CANNOT fix this, so it's only palliative care you can provide yourself and users.)
The good fix is to write a VBA macro that does the work the Conditional Formatting (CF) function does now. That is doable, though I've read it's HARD, VERY HARD, to write conditional formatting in VBA, and not easy to maintain. But it absolutely WILL fix this issue for you if you are up to it, or find someone who is. I am not that person though, so I will move on to what I do.
The problem is that these ranges CF uses become "Balkanized" and usually pretty fast. Instructing users to not cut and paste, to not drag and drop, to not insert rows, to not... well, you get the idea... it will cut down some on the problem flare-ups, but depends very much upon your users. Sooner or later, even with best intentions one will do whatever 'cause his evolved "take" on what he was trained about is that he can do so, just not save results. And he did then save without thinking fast enough. Or he was on top of that, but didn't realize how his actions ruined the logic in the spreadsheet and that, while he had done the same thing before, he had never counted on the bits that took the hit, but this time... this time it mattered and will cost the boss a couple thousand dollars or lose a customer.
And be fair (which also means "realistic"), you yourself have done precisely that. You've edited a spreadsheet, done some experimental work you did NOT intend to keep, didn't worry about how it hammered things as you only needed to see if it could do whatever it was, then you'd close, reopen, and do it for real... but saved... Training just isn't a fix. It's a help and if the people are right it's a BIG help, but it won't cover all and does nothing whatever to fix the unhappy things that do occur.
So, what I do is this:
Any range I intend to be used in CF, I create a Named Range for it. So, A1:A10? It's called "Horsey".
Make the range more readily inclusive. It needs to be A1:A10? Make it A1:A11. When people insert a row under row 10, they actually increase the size of the range to A1:A12 because row 11 was IN the range used and Excel handled the insertion the way you'd like it to.
When I set up a spreadsheet that uses CF, I immediately, after finishing the principal work, use the Macro Recorder to set up a macro recording me going to each and every rule and editing the range it applies to to be the Named Range that I defined for it.
So, say it was to be A34:A23008. I do my best to lay things out so that I could use a row above and a row below, so A33:A23009. If I can't, then I can't, but I do try. In any case, I define a Named Range for it, whatever I ended up with. In this simple example of just one rule, so just one range to worry about, I then start the Macro Recorder and bring up the CF rule (making sure to change the "Show rules for" selector from "Current selection" to "Worksheet – easy to do since it opens with that selected and I just have to press Down Arrow), type =Horsey (or whatever Name) in the Applies To : box (wiping out whatever is there), save the rule, and exit the Macro Recorder.
So, what did this accomplish?
I got a free macro. Excel did the work for me. And since it isn't a macro that I highlighted some rows for, NONE of it needs edited to generalize it. It's a finish product.
Secondly, if I go to CF, Manage Rules, and look at the Applies to box, I see the beautiful, proper range.
So if I run it again after people do things, I will get the range defined in the Named Range again, wiping out all the strange stuff. Yay!
You're likely thinking though... *"Um, at least two problems here, man... First, when I read what you wrote, you said you see the defined range, not the Named Range. Sounded shady, trick of the tongue kind of weasel language. Second thought is what about the changes, man? If they added a row, I don't want the original range, I want a range that includes that row they inserted so... what about that? And now I'm looking at the Applies to box, it doesn't have a Named Range in it at all, you WERE trying to fool me!"
Not at all. Firstly, the second concern. All those things users do that destroy your nice Applies to do NOT harm a defined Named Range. Insert a row, then delete three non-contiguous rows? Excel adjusts the definition of the Named Range to include all that activity. So when they inserted a row, the Named Range grew by that row. When you use it in the CF rule's Applies to box, it is applying the adjusted range, not the original range.
Secondly, that means the first concern is only a hassle, not a problem. When I run the macro, it overwrites with the (adjusted by Excel as needed) Named Range. Then the CF functionality immediately (immediately after saving that), changes it from the actual Named Range name to the range it refers to. So yes, it does not stay the Named Range. It DOES change to a literal range. But note that it is the RIGHT literal range: it was kept right by Excel through the Named Range functionality and the moment CF changes it to a literal range, it restores it from messy, wrong, and frankly scary, back to the simple, clear range it should be given the changes users made.
And then they probably immediately start messing it up again, but that will be addressed in a moment.
So, I now have a macro (recorded for me, not laboriously and badly written) that does that whenever I run it. If you want to use a slightly more interesting bit of VBA, you can easily and quickly learn how to add a couple lines to the macro so it runs every time the file is opened (restoring things just before use), right before it is saved (same thing), or both. Next step up in cleverness is adding a button that fires the macro any time a user thinks he ought to do so. If you already have skills or want to learn, you can even move on to setting it to look for a variety of events, like inserting or deleting rows, or cells, etc., and not having to figure out what to do for each of five, then later 12, then eventually 15, 21, 30 different events that you experience, but rather, whatever event on the list happened, just running the macro right then so the effect on your CF rules is fixed that second.
So the user inserts a row into a range of interest? The moment the insertion finishes and Excel fixes the range in the Named Range, your macro fires and reapplies the Named Ranges to each rule so the rules still work as expected.
So there's stand about wanting something (the free macro), walking slowly (a button for users to click any time they think they ought to), walking a little faster (macro fires upon opening or when saving or both), to walking noticeably fast (firing automatically when certain events occur), to something closer to power walking (adding 10-20 events over time to that automatic firing bit.
I don't use CF to much anymore, but that saved me a ton of work and a couple others a lot of concern they were getting bad conclusions from data I presented to them over the years.
However, not everyone is permitted to use macros in their spreadsheets. And that right there will kill the whole approach, eh? Still, worth arguing for if CF is important to you and your users are not too interested in how it's their fault, not yours, so you should be fire, not them. Or you shouldn't get a raise because your spreadsheets are barely usable... For me, it was bosses who paid lip service to my admonitions, but basically trampled over everything on a regular basis 'cause, well, they were the bosses. Somehow I should've done a better spreadsheet.
So, I hope you can use it. Remember, by the way, you record the macro while doing each and every one of them in one start to finish progression. Not 25 separate macros. But if you do that, or you add a few rules so need to add some macro bits, you can copy and paste pieces together. You have a macro recorded for 13 CF rules and added three new rules? Run the recorder to do the same updates to the three new CF rules, then go into VBA and copy the lines that do the work in it, go to your current macro, and paste them in. Delete the new macro (the one you recorded for the three new CF rules, NOT the UPDATED (as opposed to "new") macro that has everything.
VBA doesn't care about the order the rules are updated in, and there's no slowdown from it doing 20 changes "willy-nilly" rather than in some carefully chosen "logical" order. So just pasting them in at the end works beautifully.
If you're allowed to use macros at all.
