Using Colors to Highlight Percentages in a Range in Excel 2010

Office 2010

Office Quick Note banner

Effectively Using Colors in Microsoft Excel 2010: Learn how to highlight the values that are in the top and bottom 10 percent of a range of numbers in Microsoft Excel 2010.

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

Using colors to emphasize values that are in specific percentages of a range of numbers is a great way to quickly highlight those values. In this topic, you programmatically create a range of random numbers and then use colors to highlight the top and bottom 10 percent of values in that range. To complete this task, you must do the following:

In this task, you add programming code that highlights the values in the top and bottom percentages of a range of numbers.

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 DemoAddTop10()
      ' Fill a range with random numbers.
      ' Mark the top 10% of items in green, and the bottom
      ' 10% of the items in red.
     
      ' Set up a range, and fill it with random numbers.
      Dim rng As Range
      Set rng = Range("A1:E10")
      SetupRangeData rng
     
      ' Clear any existing format conditions.
      rng.FormatConditions.Delete
     
      ' Set up a condition that formats the top
      ' 10 percent of items on green.
      Dim fc As Top10
      Set fc = rng.FormatConditions.AddTop10
      fc.Percent = True
      fc.TopBottom = xlTop10Top
      fc.Interior.Color = vbGreen
     
      ' Set up a condition that formats the bottom
      ' 10 percent of items in red.
      Set fc = rng.FormatConditions.AddTop10
      fc.TopBottom = xlTop10Bottom
      fc.Percent = True
      fc.Interior.Color = vbRed
    End Sub
    
    Sub SetupRangeData(rng As Range)
      rng.Formula = "=RANDBETWEEN(1, 100)"
    End Sub
    
    
  5. Close the Visual Basic Editor.

In this task, you run the VBA code that creates the range of numbers and then colors the values that are in the top and bottom 10 percent.

To run the code

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

    Figure 1. Running the code highlights the top and bottom values in the range of numbers

    Highlight top and bottom values in the range
Show: