troubleshooting Question

Excel VBA - set conditional format rules precedence

Avatar of Kris Brady
Kris Brady asked on
Microsoft OfficeMicrosoft ExcelMicrosoft 365Office ProductivityVBA
3 Comments1 Solution1725 ViewsLast Modified:
[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
ASKER CERTIFIED SOLUTION
Neil Fleming
Consultant and developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros