Excel VBA - set conditional format rules precedence

Kris Brady
Kris Brady used Ask the Experts™
on
[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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Consultant and developer
Commented:
It seems to me you need a way to "name" each format condition and then re-assign the priorities based on the names.

One way to cheat this that occurs to me is to copy the priorities of the rules to some other property of the condition.

Unfortunately, there is no "name" property for a formatcondition. But the borders.color property works well as a substitute -- unless you have thousands of conditions, there is no visible difference between a border color set to "4" and a border color set to "8". They just look like black cell borders.

So for instance, this will record the priorities of all formula-based rules on the active sheet, storing them in the border color:

Sub RecordPriorities()
Dim r As Range
Dim cc As FormatCondition

Set r = ActiveSheet.Cells
With r.FormatConditions
    
    For Each cc In r.FormatConditions
    cc.Borders.Color = cc.Priority
    Next
    
End With


End Sub

Open in new window


And this will reset the priorities to whatever was first recorded:

Sub CorrectConditionNumbering()
Dim r As Range
Dim cc As FormatCondition
Dim i As Long
Dim done As Boolean

Set r = ActiveSheet.Cells
With r.FormatConditions
    While Not done
    done = True
    For i = 1 To .count
    Set cc = .Item(i)
        If cc.Priority <> cc.Borders.Color Then
        cc.Priority = cc.Borders.Color
        done = False
        End If
    Next
    Wend
End With


End Sub

Open in new window


Because of the issue you noted, where changing one priority may affect others, the second routine above loops until all priorities match the border colors.

So then you just need to trigger this routine when you refresh the table or delete a worksheet column.

Does this help?

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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial