Excel中数据数据条可以更直观的展现数据,依次单击【条件格式】>【数据条】>【实心填充】可以获得B列的效果。
这个展示效果并不理想,如果可以设置不同颜色,那么对于项目完成进度就更加一目了然,如上图中C列所示。
但是Excel默认的条件格式并不支持这种数据图+色阶的混合应用方式。
示例代码如下。
Sub Demo()
Dim c As Range
para = Array(Array(0, 0.6, vbRed), _
Array(0.6, 0.85, 6740479), _
Array(0.85, 1, 4697456))
For Each c In Range("C2:C11")
For i = 0 To 2
If c > para(i)(0) And c <= para(i)(1) Then Exit For
Next
If c.FormatConditions.Count > 0 Then c.FormatConditions.Delete
c.FormatConditions.AddDatabar
With c.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0
.MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:=1
.BarColor.Color = para(i)(2)
.BarFillType = xlDataBarFillSolid
End With
Next
End Sub
【代码解析】
第3行代码使用嵌套数组保存颜色设置,数据如果大于0,小于等于0.6,那么将使用vbRed
作为数据条颜色,依次类推。
第6~20行循环变量所有数据单元格。
第7~9行判断数组所在区间。
如果单元格中已经存在条件格式,那么第10行代码,将清除全部条件格式设置。
第11行代码添加数据条。
第12~17行代码设置数据条格式。
第13行代码设置数据条格式的最小值为0。
第14行代码设置数据条格式的最大值为1,读者需要根据数据范围进行调整,也可以使用Min和Max获取数据区域的范围。
第15行代码设置数据的颜色。
第16行代码设置颜色填充模式。
运行示例代码轻轻松松得到C列的ABC分类效果。
请大家注意这个实现方法的潜在问题,数据变更时数据条的颜色无法根据数据范围自动调整,只能再次运行代码重新设置条件格式,解决方法是在工作表的Change事件中调用此代码更新条件格式设置。