Displaying Icon Sets in Excel 2010

  • Article

Office Quick Note banner

Effectively Using Colors in Microsoft Excel 2010: Learn how to display a gallery of icon sets in Microsoft Excel 2010.

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:

  • 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 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

    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.
    rng.FormatConditions.Delete
    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.

Test the Solution

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

Next Steps