Adding Color Scale to a Range of Values in Excel 2010

Office 2010

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.

Last modified: May 12, 2011

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:

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 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.

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
Show:
© 2014 Microsoft