Displaying Icon Sets in Excel 2010

Office 2010

Office Quick Note banner

Effectively Using Colors in Microsoft Excel 2010: Learn how to display a gallery of icon sets 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

Icon sets enable you to create visual effects in your data that indicate how the value of a given cell compares with other cells. In this topic, you programmatically create a gallery of icon sets. To complete this task, you must do the following:

In this task, you add programming code that creates a gallery of icon sets.

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 TestAddIconSet()
      Dim i As Integer
      Dim rng As Range
      For i = 1 To 20
        ' Set up ranges
        Set rng = SetupRange(i)
        Select Case i
          Case 1
            SetUpIconSet rng, xl3Arrows
          Case 2
            SetUpIconSet rng, xl3ArrowsGray
          Case 3
            SetUpIconSet rng, xl3Flags
          Case 4
            SetUpIconSet rng, xl3Signs
          Case 5
            SetUpIconSet rng, xl3Stars
          Case 6
            SetUpIconSet rng, xl3Symbols
          Case 7
            SetUpIconSet rng, xl3Symbols2
          Case 8
            SetUpIconSet rng, xl3TrafficLights1
          Case 9
            SetUpIconSet rng, xl3TrafficLights2
          Case 10
            SetUpIconSet rng, xl3Triangles
          Case 11
            SetUpIconSet rng, xl4Arrows
          Case 12
            ' Reverse the order on this one:
            SetUpIconSet rng, xl4ArrowsGray, True
          Case 13
            SetUpIconSet rng, xl4CRV
          Case 14
            SetUpIconSet rng, xl4RedToBlack
          Case 15
            SetUpIconSet rng, xl4TrafficLights
          Case 16
            SetUpIconSet rng, xl5Arrows
          Case 17
            ' Reverse the order on this one:
            SetUpIconSet rng, xl5ArrowsGray, True
          Case 18
            SetUpIconSet rng, xl5Boxes
          Case 19
            SetUpIconSet rng, xl5CRV
          Case 20
            SetUpIconSet rng, xl5Quarters
        End Select
      Next i
    End Sub
    Function SetupRange(col As Integer) As Range
        ' Set up ranges, filled with numbers from 1 to 10.
        Set rng = Range(Cells(1, col), Cells(10, col))
        Dim rng1 As Range
        Set rng1 = Cells(1, col)
        rng1.Value = 1
        Dim rng2 As Range
        Set rng2 = Cells(2, col)
        rng2.Value = 2
        Range(rng1, rng2).AutoFill Destination:=rng
        Set SetupRange = rng
    End Function
    Sub SetUpIconSet(rng As Range, iconSet As XlIconSet, Optional ReverseOrder As Boolean = False)
        ' Set up an icon set for the supplied range.
        Dim isc As IconSetCondition
        Set isc = rng.FormatConditions.AddIconSetCondition
        With isc
            ' If specified, show the icons in the reverse ordering:
            .ReverseOrder = ReverseOrder
            .ShowIconOnly = False
            ' Select the requested icon set:
            .iconSet = ActiveWorkbook.IconSets(iconSet)
        End With
    End Sub
  5. Close the Visual Basic Editor.

In this task, you run the VBA code that displays the various icon sets.

To run the code

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

    Figure 1. Running the code creates the gallery of icons

    Code creates the gallery of icons