Color Formatting for Range Averages in Excel 2010

  • Article

Office Quick Note banner

Effectively Using Colors in Microsoft Excel 2010: Learn how to add color to value averages in a Microsoft Excel 2010 range.

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 values and then color those values above and below an average. 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 colors those values above and below an average number.

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 TestAboveAverage()
    ' Fill the range with random numbers between
    ' -50 and 50.
    Dim rng As Range
    Set rng = Range("A1", "A20")
    SetupRandomData rng

    ' Create a conditional format for values above average.
    Dim aa As AboveAverage
    Set aa = rng.FormatConditions.AddAboveAverage
    aa.AboveBelow = xlAboveAverage
    aa.Font.Bold = True
    aa.Font.Color = vbRed

    ' Create a conditional format for values below average.
    Dim ba As AboveAverage
    Set ba = rng.FormatConditions.AddAboveAverage
    ba.AboveBelow = xlBelowAverage
    ba.Font.Color = vbBlue
End Sub

Sub SetupRandomData(rng As Range)
    rng.Formula = "=RANDBETWEEN(-50, 50)"
End Sub

  5. Close the Visual Basic Editor.

Test the Solution

In this task, you run the VBA code that creates the range of values and applies color to those values.

To run the code

  • On the Developers tab, click Macros, select TestAboveAverage, and then click Run. Examine the results as shown in Figure 1. The values above the average are colored red and the values below the average are colored blue.

    Figure 1. Running the code creates a range of colored values

    Code creates a range of colored values

Next Steps