This may be an Office 2016 issue; I can't replicate the issue in Office 2013
This is definitely an Office 2016 issue; if product is developed in Office 2013, the issue does not present itself.
I want use VBA to set the precedence order of the conditional formatting rules on a worksheet.
When using a data connection to update a table, or when deleting columns, the order of the conditional formatting rules is modified. This is unwanted behavior as one of the rules is set to [StopIfTrue] and I don't want the rules that appear below this [StopIfTrue] rule to be applied.
I will not be uploading a file with all of the code as it's our secret sauce. I'll do my best to provide a clear example:
Note: All of the rules apply to a table on the worksheet.
After populating table or deleting columns (either process triggers the re-ordering):
Note that the above order can differ - the order is not consistently reset (e.g., sometimes cf_rule08 goes to the bottom of the list and/or cf_rule09 moves above cf_rule 08 and/or cf_rule04 moves below cf_rule 07); the new precedence order appears to be random (but it can't be, can it?).
The screenshot below contains the values for the rules both prior to any modification (Original) and after deleting columns (or populating the table):
The first numeric value shown is the Priority; the second numeric value that appears in most of the screenshots is the type.
Short of reading every rule each time a change is made (which is frequently) and manually setting the precedence order, I can't find a solution. Even this approach is a bit wonky as modifying the precedence order of one rule can change the precedence order of another rule so knowing which rule is in effect when correcting the Priority is not as simple as using something like...[Worksheets("Sheet1
riority = 1
...unless of course I read the AppliesTo.Address and Formula1 values, create a "done" variable for each rule and read through each rule - this would be a headache indeed.
I thought about looping through the rules and setting the SetLastPriority for each rule in reverse order, but the same roadblock mentioned above rears its ugly head. I don't know how to identify which rule is which once Excel does it own sorting on the rules.
There must be an easy solution that I'm simply overlooking. Any help is most appreciated.