Adding Color Scale to a Range of Values in Excel 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
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
Start Excel 2010.
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.
In the Projects pane, click Sheet1.
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
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