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?

  • 3
    No, this isn't possible through conditional formatting as relative references aren't allowed in color scales. It is possible, however, by using a macro to create a gradient.. Oct 10, 2013 at 9:18

1 Answer 1


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.