Conditional Formatting Rules Changing Order Randomly

Copper Contributor

Hello all,

So I'm really stuck here and have no idea what to do. I'm by no means an excel guru so please don't be too technical with responses, if it can be helped. 

So here's the gist. I'm making a sort of mock videogame on excel. I've been mostly successful, but on the monster attack screen, I've noticed that my meticulously placed conditional formatting rules are changing randomly in order for absolutely no discernible reason. This is extremely problematic to me, because this screen has, very literally, hundreds of formatting rules, and them being in the correct order is quintessential to it functioning properly.

At the most basic level, a CHOOSE(RANDBETWEEN( function is selecting a character as a target for an attack, and another CHOOSE(RANDBETWEEN( function is selecting a specific attack. The damages of all possible attacks against all possible targets exists in a series of columns, but they're all in white font until that specific target and specific attack are randomized, in which case they turn black and become readable to the player. The idea is that the damage of that attack against that specific target is meant to appear only when that specific target and attack are randomized, and remain white any time they aren't as to not muddy the hell out of the game with a bunch of irrelevant values. When the rules remain in the correct order, this works without a problem; when they change order without me doing anything, however, it all falls apart. 

I've gone through the worksheet on multiple occasions and cell-by-cell correctly reordered the rules (a miserable process that took about an hour each time) but every time I fixed some rules, other rules that I had previously fixed randomly reordered themselves again. 

I'm at my wit's end with this. If anyone has any idea what's happening and how I can get the rules to stop changing order on their own, I would be extremely appreciative. 

5 Replies
I would love to see this workbook!! Although there are a lot of quirks with conditional formatting, randomly changing order isn't one I've noticed. I have a problem with inserting or deleting rows causing them to procreate like rabbits so a sheet that should only have 4 rules ends up with 100s and instead of rules with 1 area they have dozens. To this end I created a macro that I can convert from conditional rules to a worksheet and then those saved parameters in the worksheet to restore the conditional rules. I have not checked to see if such macro always produces the same conditional rule order but I suspect it would. Alternatively I could imaging a macro that would just reorder these rules.
But I'm still surprised and wondering how/why this reorder is happening.
I have no experience at all with macros, unfortunately. I don't know how to build them or utilize them. But yeah, it's driving me crazy. I haven't had this problem with a single other portion of the game. It's only that sheet. I can't for the life of me figure out why it's happening.

@DDHoward I found that workbook i did those macros in.  Sheet 2 has the Conditional Formatting rules and Macro buttons to Clear existing CFs, Read Present rules, and Apply Rules.  I use my own 'language' for defining formats and such but seem to have added some decent notes on all that.

I really appreciate this! I'm not sure if I'll be able to use it for this project but regardless I'm thankful.



Same problem here.

Drives me crazy.

The problem is known for at least 7 years.

Microsoft doesn't seem to care about bugs, if the majority of users won't run into them regularly (there are more excamples for this experience