![Excel:双级颜色标尺](https://linux22.com/image/1381352/Excel%EF%BC%9A%E5%8F%8C%E7%BA%A7%E9%A2%9C%E8%89%B2%E6%A0%87%E5%B0%BA.png)
是否可以对同一单元格中的两个分级色标进行条件格式化?
例如,我希望有条件地格式化一个单元格,使其颜色为:绿色到蓝色,值从 0 到 10 的分级比例和红色到黄色的渐变颜色代表值 10 到 20。
如果我输入两个单独的双色比例规则,它不起作用,因为每个规则都适用于每个可能的值(您只能指定分级开始和结束的点 - 开始和结束颜色适用于这些范围之外的所有值)。
我希望开始和结束的颜色不是在评分范围之外申请,以便可以使用后续规则进行评分。
这可能吗?
答案1
好吧,我有点厌倦这个问题了。我写了这个宏,它按升序排序,将非空白范围分成两半(四舍五入)并应用两个色阶。您可以根据需要进行修改。
Sub TestColorScale()
Application.ScreenUpdating = False
'sort ascending
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A:A")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'count cells
Dim intcount As Integer
Dim rngcount As Range
Set rngcount = Range("A:A")
intcount = Application.WorksheetFunction.CountA(rngcount)
rngcount.FormatConditions.Delete
'Get half
Dim inthalf As Integer
inthalf = intcount / 2
inthalf = Application.WorksheetFunction.RoundUp(inthalf, 0)
'Do first half
Dim rng1 As Range
Set rng1 = Range(Cells(1, 1), Cells(inthalf, 1))
' Add a 2-color scale.
Dim cs1 As ColorScale
Set cs1 = rng1.FormatConditions.AddColorScale(ColorScaleType:=2)
' Format the first color
With cs1.ColorScaleCriteria(1)
.Type = xlConditionValueLowestValue
With .FormatColor
.Color = vbGreen
.TintAndShade = -0.25
End With
End With
' Format the second color
With cs1.ColorScaleCriteria(2)
.Type = xlConditionValueHighestValue
With .FormatColor
.Color = vbBlue
.TintAndShade = 0
End With
End With
'Do second half
Dim rng2 As Range
Set rng2 = Range(Cells(inthalf + 1, 1), Cells(intcount, 1))
' Add a 2-color scale.
Dim cs2 As ColorScale
Set cs2 = rng2.FormatConditions.AddColorScale(ColorScaleType:=2)
' Format the third color
With cs2.ColorScaleCriteria(1)
.Type = xlConditionValueLowestValue
With .FormatColor
.Color = vbRed
.TintAndShade = -0.25
End With
End With
' Format the fourth color
With cs2.ColorScaleCriteria(2)
.Type = xlConditionValueHighestValue
With .FormatColor
.Color = vbYellow
.TintAndShade = 0
End With
End With
Application.ScreenUpdating = True
End Sub