Excel: Dual graded color scales

conditional formattingmicrosoft excelmicrosoft-excel-2007vba

Is it possible to do conditional formating for two graded color scales over the same cells?

For example I wish to conditionally format a cell such that its color is:
Green to Blue graded scale for values 0 through 10 and Red to Yellow graded color for values 10 to 20.

If I in enter two seperate 2-color scale rules, it doesn't work, as each rule applies to every possible value (you can only specify the points at which the grading starts and end – the start and end color apply to all values outside of those ranges).

I wish for the start and end color not to apply outside of the grading range, so that they can be graded with a subsequent rule.

Is this possible?

Best Answer

All right, I got kind of sick of this question sticking out there. I wrote this macro, it sorts ascending, divides the non-blank range in half (rounded up) and applies two color scales. Modify as you wish.

Sub TestColorScale()

Application.ScreenUpdating = False

'sort ascending
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A:A")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
    End With

'count cells
Dim intcount As Integer
Dim rngcount As Range
Set rngcount = Range("A:A")
intcount = Application.WorksheetFunction.CountA(rngcount)

'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