Microsoft Office Professional 2010 Conditional for

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,Microsoft Office 2007 Professional, 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,Microsoft Office Professional 2010, 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.AddDatabarEdit ing the rule:To edit the rule, index into the FormatConditions collection and modify the properties. For example, to change the bar color,Microsoft Office 2007 Professional, run:Range("A1:A5").FormatConditions(1).BarColor.Co lorIndex = 3Here, 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,Microsoft Office Enterprise 2007, run:?Range("A1:A5").FormatConditions(1).PriorityTo make this rule the lowest priority:Range("A1:A5").FormatConditions(1).SetLas tPriorityTo assign a specific priority:Range("A1:A5").FormatConditions(1).Priori ty = 3Note 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 methodRange("A1:A5").FormatConditions(1).DeleteTo delete all rules in the specific range,Microsoft Office Professional Plus 2010, 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.Color Index = 3 End SubHopefully 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. <div