We help IT Professionals succeed at work.

Excel VBA - set conditional format rules precedence

1,669 Views
Last Modified: 2018-05-22
[UPDATE]
This may be an Office 2016 issue; I can't replicate the issue in Office 2013
[UPDATE]

[UPDATE 02]
This is definitely an Office 2016 issue; if product is developed in Office 2013, the issue does not present itself.
[UPDATE 02]

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.
Original Order:
cf_rule01
cf_rule02
cf_rule03
cf_rule04
cf_rule05
cf_rule06
cf_rule07
cf_rule08 [StopIfTrue=True]
cf_rule09
cf_rule10

After populating table or deleting columns (either process triggers the re-ordering):
cf_rule01
cf_rule02
cf_rule03
cf_rule04
cf_rule05
cf_rule06
cf_rule08
cf_rule10
cf_rule07
cf_rule09


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.
Before and After
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").Range("tbl_01").FormatConditions(1).Priority = 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.

Kris
Comment
Watch Question

Consultant and developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
I apologize for the delayed response.  Thank you doesn't seem to cover it.  This really had me stumped.  I'll be implementing your solution this weekend.

Thanks again.

Author

Commented:
Thank you.  Love the creativity of this solution!