在VBA中使用條件格式的示例_Excel 2007新知
譯者：Kevin? 來源：http://blogs.msdn.com/excel 發表于：2006年7月7日
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. 自從我收到一些關于“如何在VBA中使用新的條件格式”的詢問，我就想提供給大家一些簡單的示例。在我們Excel開發團隊中有一條法則：當我們增加任何新功能時，我們必須確定它們在被程序調用時能和在用戶界面中工作的一樣好。Excel 12的對象模型支持所有出現在用戶界面中的條件格式功能，包括增加、和s刪除規則，或者更改規則的優先級。
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. 在舊版本Excel中寫過與條件格式相關的VBA代碼的人會知道，條件格式集不能使用Range對象。讓我通過運行一些簡單示例來展示如何使用條件格式集中的新功能。
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
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. 現有規則： 規則是通過定位條件格式集的索引號并修改其屬性。比如，更改data bar的顏色： Range(“A1:A5”).FormatConditions(1).BarColor.ColorIndex = 3 在這里，數字1表示區域中的第一個規則。
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 規則優先級： 在Excel 12里，規則優先級這個概念表示支持在一個區域建立多重條件，優先級決定規則執行的次序。在對象模型里，我們同樣可以使用條件格式對象的優先級屬性。此屬性在工作表級被追蹤，比如，檢驗某條規則的優先級： Range(“A1:A5”).FormatConditions(1).Priority 將某規則降至最低優先級： Range(“A1:A5”).FormatConditions(1).SetLastPriority 分配一項指定的優先級： Range(“A1:A5”).FormatConditions(1).Priority = 3 注意，如果你只有3條規則，那么設置優先級為3和設置優先級為最低的效果是一樣的。 刪除規則： 你可以根據索引號并使用Delete方法來刪除一個指定的規則： Range(“A1:A5”).FormatConditions(1).Delete 也可以將Delete方法作用于條件格式集來刪除指定區域中的所有規則： 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: 下面是另一個示例。假設你想用VBA來把單元格區域A1-A10中數值最高的5%突現并填充為紅色，以下是相關的代碼：
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. 希望這些對您有所幫助。
Published Friday, October 14, 2005 1:42 PM by David Gainer
