Displaying Icon Sets in Excel 2010
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
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 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
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