This documentation is archived and is not being maintained.

# Adding Color Scale to a Range of Values in Excel 2010

Office 2010

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

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

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

Dim cs As ColorScale

' 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.
End With
End With

' Format the second color as green, at the highest value.
With cs.ColorScaleCriteria(2)
.Type = xlConditionValueHighestValue
With .FormatColor
.Color = vbGreen
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 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
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
End With
End With

' At the highest value, the color should be green.
With cs.ColorScaleCriteria(3)
.Type = xlConditionValueHighestValue
With .FormatColor
.Color = &H7BBE63
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

Show: