Excel2007新知.docxVIP

Excel2007新知：在VBA中使用条件格式的示例译者：Kevin? 来源：/excel/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 Addobjectname 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.AddDatabarEditing 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 = 3Here, 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 p