☰

Excel：双级颜色标尺

tag-icon • tag-icon microsoft-excel microsoft-excel-2007 vba conditional-formatting
Excel：双级颜色标尺

是否可以对同一单元格中的两个分级色标进行条件格式化？

例如，我希望有条件地格式化一个单元格，使其颜色为：绿色到蓝色，值从 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

相关内容