Icon
Shows the collection of IconCriterion objects. Each IconCriterion object represents the values and threshold type for each icon in an icon set conditional formatting rule. The IconCriteria collection is returned from the IconCriteria property of the IconSetCondition object. You can access each IconCriterion object in the collection by passing an index (a positive integer) into the collection.
Lets put code around to automate the same. In this article we are using VSTO (Visual Studio Tools for office) in both flavors (C#, VB.Net) and VBA (Visual Basic for Applications) hence following prerequisites are recommended:
- Visual Studio 2015 or above having Microsoft Office for Developer Tool installed
- Create Excel Addin in C# or VB code style (Visual Studio Tools for Office)
- Excel 2010 or above
- Create a ribbon designer and put button
C# code example
private void btnIcon_Click(object sender, RibbonControlEventArgs e) { //Retain selection excel.Range oRange = Globals.ThisAddIn.Application.Selection; //Get current workbook excel.Workbook oWorkbook = Globals.ThisAddIn.Application.ActiveWorkbook; if (oRange != null) { //Create an icon set conditional format on selection excel.IconSetCondition oConSetCondition = oRange.FormatConditions.AddIconSetCondition(); //Change the icon set to a five arrow icon set oConSetCondition.IconSet = oWorkbook.IconSets[excel.XlIconSet.xl5Arrows]; //modify each IconCriteria oConSetCondition.IconCriteria[2].Type=excel.XlConditionValueTypes.xlConditionValueNumber; oConSetCondition.IconCriteria[2].Value = 16; // Operators and constants // xlBetween = 1 // xlNotBetween = 2 // xlEqual = 3 // xlNotEqual = 4 // xlGreater = 5 // xlLess = 6 // xlGreaterEqual = 7 // xlLessEqual = 8 oConSetCondition.IconCriteria[2].Operator = 7; oConSetCondition.IconCriteria[3].Type = excel.XlConditionValueTypes.xlConditionValueNumber; oConSetCondition.IconCriteria[3].Value = 70; oConSetCondition.IconCriteria[3].Operator = 7; oConSetCondition.IconCriteria[4].Type = excel.XlConditionValueTypes.xlConditionValueNumber; oConSetCondition.IconCriteria[4].Value = 80; oConSetCondition.IconCriteria[4].Operator = 7; oConSetCondition.IconCriteria[5].Type = excel.XlConditionValueTypes.xlConditionValueNumber; oConSetCondition.IconCriteria[5].Value = 90; oConSetCondition.IconCriteria[5].Operator = 7; } else { MessageBox.Show(@"Please select a valid range!!!"); } }
VB.Net code example
Private Sub btnIcon_Click(sender As Object, e As RibbonControlEventArgs) Handles btnIcon.Click 'Retain selection Dim oRange as excel.Range oRange = Globals.ThisAddIn.Application.Selection 'Get current workbook dim oWorkbook as excel.Workbook oWorkbook = Globals.ThisAddIn.Application.ActiveWorkbook If not oRange Is Nothing Then 'Create an icon set conditional format on selection dim oConSetCondition as excel.IconSetCondition oConSetCondition = oRange.FormatConditions.AddIconSetCondition() 'Change the icon set to a five arrow icon set oConSetCondition.IconSet = oWorkbook.IconSets(excel.XlIconSet.xl5Arrows) 'modify each IconCriteria oConSetCondition.IconCriteria(2).Type=excel.XlConditionValueTypes.xlConditionValueNumber oConSetCondition.IconCriteria(2).Value = 16 ' Operators and constants ' xlBetween = 1 ' xlNotBetween = 2 ' xlEqual = 3 ' xlNotEqual = 4 ' xlGreater = 5 ' xlLess = 6 ' xlGreaterEqual = 7 ' xlLessEqual = 8 oConSetCondition.IconCriteria(2).Operator = 7 oConSetCondition.IconCriteria(3).Type = excel.XlConditionValueTypes.xlConditionValueNumber oConSetCondition.IconCriteria(3).Value = 70 oConSetCondition.IconCriteria(3).Operator = 7 oConSetCondition.IconCriteria(4).Type = excel.XlConditionValueTypes.xlConditionValueNumber oConSetCondition.IconCriteria(4).Value = 80 oConSetCondition.IconCriteria(4).Operator = 7 oConSetCondition.IconCriteria(5).Type = excel.XlConditionValueTypes.xlConditionValueNumber oConSetCondition.IconCriteria(5).Value = 90 oConSetCondition.IconCriteria(5).Operator = 7 else MsgBox("Please select a valid range!!!") End If End Sub
VBA code example
Sub CreateIconSetCF() Dim oIconSet As IconSetCondition 'Bind workbook reference Dim oWorkbook As Workbook Set oWorkbook = ActiveWorkbook 'bind selection Dim oRange As Range Set oRange = Selection 'Create an icon set conditional format on selection Set oIconSet = oRange.FormatConditions.AddIconSetCondition 'Change the icon set to a five arrow icon set oIconSet.IconSet = oWorkbook.IconSets(xl5Arrows) 'modify each IconCriteria ' Operators and constants ' xlBetween = 1 ' xlNotBetween = 2 ' xlEqual = 3 ' xlNotEqual = 4 ' xlGreater = 5 ' xlLess = 6 ' xlGreaterEqual = 7 ' xlLessEqual = 8 With oIconSet.IconCriteria(2) .Type = xlConditionValueNumber .Value = 60 .Operator = 7 End With With oIconSet.IconCriteria(3) .Type = xlConditionValueNumber .Value = 70 .Operator = 7 End With With oIconSet.IconCriteria(4) .Type = xlConditionValueNumber .Value = 80 .Operator = 7 End With With oIconSet.IconCriteria(5) .Type = xlConditionValueNumber .Value = 90 .Operator = 7 End With End Sub
Output

Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.