欧非资源网:安全、免费、专业放心的资源下载站! 最新软件|软件分类

您的位置:欧非资源网 > Excel专区 > Excel教程 > excel如何利用VBA批量设置图标集条件格式

excel如何利用VBA批量设置图标集条件格式

时间:2018-04-04 22:47作者:admin来源:未知人气:624我要评论(0)

在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列中设置图标集,最后设置适当的列宽即可。

相关阅读 你想学的FILTER函数用法,很全了当Excel体积臃肿,运行卡顿时,我们应该做些什么?带合并单元格的表格,求和计数也不难​Excel筛选条件怎么设置​ExcelA4页面怎么设置​Excel表格A列被隐藏了怎么恢复​Excel图例位于底部怎么设置​Excel图表怎么嵌入单元格​Excel没保存不小心关闭怎么恢复​Excel图表怎么设置X轴Y轴

文章评论
发表评论

热门文章 excel怎么制作表格?制作excel表格的步骤技巧excel 常见的六个排序方法,你必须要学会excel自动换行显示不全怎么办excel 数据透视表 选定区域不能分组 的原因和解决方法

最新文章 你想学的FILTER函数用法,很全了当Excel体积臃肿,运行卡顿时,我们应该做些什么? 带合并单元格的表格,求和计数也不难​Excel筛选条件怎么设置​ExcelA4页面怎么设置​Excel表格A列被隐藏了怎么恢复

人气排行 excel怎么制作表格?制作excel表格的步骤技巧excel 常见的六个排序方法,你必须要学会excel自动换行显示不全怎么办excel 数据透视表 选定区域不能分组 的原因和解决方法Excel高级筛选怎么用 excel高级筛选多个条件(并且、或用法)excel if函数的多个条件使用方法Excel中的快速填充键ctrl+e没反应,不能用是为什么?excel 判断两列数据是否一致的四种方法

盖楼回复X

(您的评论需要经过审核才能显示)