Adding Color Scale to a Range of Values in Excel 2010

  • Article

Office Quick Note banner

Effectively Using Colors in Microsoft Excel 2010: Learn how to use a color scale gradient to highlight a specific set of numbers in a range of Microsoft Excel 2010 values.

Applies to: Excel 2010 | Office 2010 | VBA

In this article
Add the Code to the Visual Basic Editor
Test the Solution
Next Steps

Published:   May 2011

Provided by:    Frank Rice, Microsoft Corporation

Adding color to numbers in a range is an effective way to highlight the values that fit within a given criterion. In this topic, you programmatically create a range of numbers and then add a color scale gradient that changes color as the values increase. To complete this task, you must do the following:

  • Add the Code to the Visual Basic Editor

  • Test the Solution

Add the Code to the Visual Basic Editor

In this task, you add programming code that creates a range of numbers and then applies a color gradient to the values that are above and below the 50th percentile.

To add code to the Visual Basic Editor

  1. Start Excel 2010.

  2. On the Developer tab, click Visual Basic to open the Visual Basic Editor.

    Note

    If you do not see the Developer tab in Excel 2010, click the File tab, and then click Options. In the categories pane, click Custom Ribbon, select Developer, and then click OK.

  3. In the Projects pane, click Sheet1.

  4. Paste or type the following Microsoft Visual Basic for Applications (VBA) code into the module window.

    Sub TestColorScale()
    ' Fill a range with numbers from 1 to 50.
    Dim rng As Range
    Set rng = Range("A1:A50")

    Range("A1") = 1
    Range("A2") = 2
    Range("A1:A2").AutoFill Destination:=rng

    rng.FormatConditions.Delete

   ' Add a 2-color scale.
    Dim cs As ColorScale
    Set cs = rng.FormatConditions.AddColorScale(ColorScaleType:=2)

    ' Format the first color as light red
    With cs.ColorScaleCriteria(1)
        .Type = xlConditionValueLowestValue
        With .FormatColor
            .Color = vbRed
            ' TintAndShade takes a value between -1 and 1.
            ' -1 is darkest, 1 is lightest.
            .TintAndShade = -0.25
        End With
    End With

    ' Format the second color as green, at the highest value.
    With cs.ColorScaleCriteria(2)
        .Type = xlConditionValueHighestValue
        With .FormatColor
            .Color = vbGreen
            .TintAndShade = 0
        End With
    End With

    ' Try again with a rectangular range of values.
    ' Lowest values should be red, values at the 50th percentile
    ' should be red/green, high values are green.
    Set rng = Range("D1", "H10")
    rng.Formula = "=RANDBETWEEN(1, 99)"
    rng.FormatConditions.Delete
    Set cs = rng.FormatConditions.AddColorScale(ColorScaleType:=3)

    ' Set the color of the lowest value, with a range up to
    ' the next scale criteria. The color should be red.
    With cs.ColorScaleCriteria(1)
        .Type = xlConditionValueLowestValue
        With .FormatColor
            .Color = &H6B69F8
            .TintAndShade = 0
        End With
    End With

    ' At the 50th percentile, the color should be red/green.
    ' Note that you cannot set the Value property for all
    ' values of Type.
    With cs.ColorScaleCriteria(2)
        .Type = xlConditionValuePercentile
        .Value = 50
        With .FormatColor
            .Color = &H84EBFF
            .TintAndShade = 0
        End With
    End With

    ' At the highest value, the color should be green.
    With cs.ColorScaleCriteria(3)
        .Type = xlConditionValueHighestValue
        With .FormatColor
            .Color = &H7BBE63
            .TintAndShade = 0
        End With
    End With
End Sub

  5. Close the Visual Basic Editor.

Test the Solution

In this task, you run the VBA code that creates the range of number and then adds a color scale to those values.

To run the code

  • On the Developers tab, click Macros, select TestColorScale, and then click Run. Examine the results shown in Figure 1.

    Figure 1. Running the code applies a color scale to a range of numbers

    Code applies a color scale to a range of values

Next Steps