您的位置: 首页 > EXCEL技巧 > ExcelVBA >

VBA批量设置图标集条件格式

时间:2014-10-12 整理:docExcel.net

在Excel 2007/2010中设置图标集条件格式时,通常是同时对区域中的全部单元格进行设置的。但有时也有例外,如下图中C2:C1000区域中的各单元格的值为同行A列与B列的差值,需要在C列中设置图标集:

    

要求如下图:

在这种情况下,由于C列单元格中的图标集仅仅与其他列中的单元格值有关,而与本列中的其他单元格无关,因而无法直接对C2:C1000区域同时设置图标集。如果手动对C列中的每个单元格逐一设置图标集,在数据量较大的情况下几乎无法实现。要实现这个目的,可以用下面的两种方法:

方法一:用VBA代码

1.按Alt+F11,打开VBA编辑器,在代码窗口中粘贴下列代码:

Sub IconSet()
Dim rCell As Range
Application.ScreenUpdating = False
Range("c2:c1000").FormatConditions.Delete
For Each rCell In Range("c2:c1000")
   rCell.FormatConditions.AddIconSetCondition
   With rCell.FormatConditions(1)
       .IconSet = ActiveWorkbook.IconSets(xl3Symbols2)
       With .IconCriteria(2)
         .Type = xlConditionValueNumber
         .Value = 0
         .Operator = 7
       End With
       With .IconCriteria(3)
         .Type = xlConditionValueFormula
         .Value = "=OFFSET($A$1,ROW()-1,0)*0.2"
         .Operator = 5
       End With
   End With
Next
Application.ScreenUpdating = True
End Sub

2.关闭VBA编辑器,按Alt+F8,打开“宏”对话框,选择“IconSet”宏名执行代码。

上述代码对C2:C1000区域中各单元格逐一设置图标集,选择C列中的某个单元格,如C8,在“开始”选项卡的“样式”组中单击“条件格式→管理规则”,在弹出的对话框中单击“编辑规则”,可以看到上述代码所设置的图标集条件格式,如图。

由于在条件格式的图标集、色阶、数据条中无法使用相对引用,因而使用下面的公式:

=OFFSET($A$1,ROW()-1,0)*0.2

该公式在图标集条件格式中总可以引用同行A列单元格的值。

方法二:用辅助列

方法是在C列在左侧插入一个辅助列,然后在辅助列中设置图标集条件格式,设置后看上去类似直接在D列中设置的图标集。

1.在C列左侧插入辅助列,先前的C列变为D列。选择C2单元格,在其中输入公式:

=D2/A2

然后向下填充公式到C1000单元格。

2.选择C2:C1000区域,在“开始”选项卡的“样式”组中单击“条件格式→新建规则”,在弹出的对话框中,默认已选择了“基于各自值设置所有单元格格式”。在下方的区域中,选择“格式样式”为“图标集”,“图标样式”选择“三个符号(无圆圈)”,类型都选择为“数字”,分别设置为“0.2”和“0”。勾选“仅显示图标”,如下图。

3.单击“确定”。Excel会在C列中设置图标集,最后设置适当的列宽即可。


VBA批量设置图标集条件格式 问题:VBA批量设置图标集条件格式
回答:在Excel 2007/2010中设置图标条件格式时,通常是同时对区域中的全部单元格进行设置的。但有时也有例外,如下图中C2:C1000区域中的各单元格的值为同行A列与B列的差值,需要在C列中设置图标集:    要求如下图: 在...
Excel2013中用红绿灯图标来标记员工完成情况 问题:Excel2013中用红绿灯图标来标记员工完成情况
回答:...间不知所措,不要慌,下面,Word联盟小编来教大家使用图标集来完成目的。 ①首先启动Excel2013,建立一个简单的表格,选中其中的数据区域,单击菜单栏--开始--条件格式--图标集,选择其他规则。 ②弹出新建格式规则对话...
在Excel2013中利用图标集来标记状态 问题:在Excel2013中利用图标集来标记状态
回答:...用高级版本的时候,发现很多新功能,都不知道怎么用。图标集是2007版本新增的功能,它可以在单元格内加上各式各样的图标,形象的表示状态等等。下面,我就在Excel2013中为大家演示一次。 ①启动Excel2013,创建下图所示的表...
Excel 2007 常用 问题:Excel 2007 常用
回答:    (2) 假如此时添加的图标不能需要,您还可以单击“样式”组中的【条件名目】按钮,在弹出的下拉菜单中执行【法则】呼吁,打开“条件名目法则器”对话框。          (...
Excel2010视频教程:8、快速分析数据之图标集 问题:Excel2010视频教程:8、快速分析数据之图标集
回答:...数据分析的时候,对于某一些特殊的数据,可能需要使用图标来进行下标示,此时,我们可以通过条件格式当中的图标集来完成,在Excel2010当中,不仅丰富了图标集的样式而且还增加了许多新的功能,利用这些功能,我们可以更...
Excel2007中制作自动判断对错的测试题 问题:Excel2007中制作自动判断对错的测试题
回答:...G4,确定。 ⑦选中整个E列,单击菜单栏---条件格式---图标集---其他规则。 ⑧弹出新建格式规则对话框,编辑规则,如下图所示,单击确定。 ⑨我们来测试看看,中国有56个民族,勾选第3个选项,自动打钩,证明回答正确...
相关推荐: