Conditional formatting using VBA - some examples
Since I have had some comments and emails asking about how the new conditional formatting features could be accessed using VBA, I wanted to provide a few brief examples. One of the Excel team’s principles is that when we add new features, we make sure that they are available programmatically as well as in the user interface. The Excel 12 object model, accordingly, supports all conditional formatting functionality that is supported in the UI. This includes creating, editing, or deleting rules, or changing priorities on rules.
As folks who have written conditional formatting VBA in previous versions of Excel will know, the FormatConditions collection hangs off the Range object. Let me briefly run through some examples of how our new functionality is exposed in the FormatConditions collection.
Creating a rule:
The new conditional formatting rules we have introduced in Excel 12 (Databars, Color Scales, Icon Sets, Top n, etc.) can be created using the Add<objectname> method in the FormatConditions collection. For example, to add a Databar, run:
Range("A1:A5").FormatConditions.AddDatabar
Editing the rule:
To edit the rule, index into the FormatConditions collection and modify the properties. For example, to change the bar color, run:
Range("A1:A5").FormatConditions(1).BarColor.ColorIndex = 3
Here, the number 1 indexes the first rule on the range.
Editing the priority:
In Excel 12, we introduced the idea of rule priorities to support multiple conditions on a range. The priority determines the order of application of the rule. In the object model, we also have the Priority property on the FormatConditions object. This property is tracked at a sheet level. For example, to verify the priority of a rule, run:
?Range("A1:A5").FormatConditions(1).Priority
To make this rule the lowest priority:
Range("A1:A5").FormatConditions(1).SetLastPriority
To assign a specific priority:
Range("A1:A5").FormatConditions(1).Priority = 3
Note that if you had three rules, setting the priortity to be 3 and using SetLastPriority would have the same effect.
Deleting the rule:
You can delete a specific rule by indexing into it and then calling the Delete method
Range("A1:A5").FormatConditions(1).Delete
To delete all rules in the specific range, call the Delete method on the FormatConditions collection.
Range("A1:A5").FormatConditions.Delete
Here’s another example. Imagine you wanted to write VBA to highlight the Top 5% of the values in the range A1-A10 with a red fill. Here is the code snippet for this:
Sub Top5Percent()
'Adding the Top10 rule to the range
Range("A1:A10").FormatConditions.AddTop10
'Assign the rank of the condition to 5
Range("A1:A10").FormatConditions(1).Rank = 5
‘Set the Percent property true. It is false by default.
Range("A1:A10").FormatConditions(1).Percent = True
'Set the color to a red fill
Range("A1:A10").FormatConditions(1).Interior.ColorIndex = 3
End Sub
Hopefully these examples are useful.
One other thing before I sign off - Jensen Harris just posted a topic on keyboard access and the ribbon, which is something that is important to Excel users, so you might want to take a look.
