在VBA中使用条件格式的示例_Excel 2007新知

 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<objectname> method in the FormatConditions collection. For example, to add a Databar, run:
Range("A1:A5").FormatConditions.AddDatabar
增加一个规则：
Excel 12中，新的条件格式的规则（Data bars, Color Scales, Icon Sets, Top n等待），可以在条件格式集中使用Add <对象名>的方法来创建。比如，创建一个data bar：
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.
编辑现有规则：
编辑规则是通过定位条件格式集的索引号并修改其属性。比如，更改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

注：本文翻译自http://blogs.msdn.com/excel ，原文作者为David Gainer（a Microsoft employee)，