Meraz Al Nahian
The article will show you how to use Conditional Formatting Icon Sets more than 3 in Excel. Normally we use Icon Sets to emphasize the significance of a data in an Excel sheet. Generally, you can use 3 to 5 icons while using Excel Conditional Formatting. In this article, we will be describing the processes of applying Icon Sets of more than 3 in a dataset.
Download Practice Workbook
2 Ways to Use Conditional Formatting with More than 3 Icon Sets in Excel
In the dataset, you will see the performances of some students in an Exam. We are going to apply Conditional Formatting Icon Sets based on their performances. Notice that the performance category is divided into 5 classes.
1. Using Conditional Formatting Rule to Apply More than 3 Icon Sets
We can use Conditional Formatting Icon Sets more than 3 simply by using the Conditional Formatting Rule. Because Excel has features to use 3 to 5 Icon Sets. Let’s go through the process below for a better understanding.
Steps:
- First, we are going to classify these performances by numbers, because it’s easier for Excel to operate on numbers.
- Also, create a column to store the Performance Rating.
- Next, type the following formula in cell D5.
=VLOOKUP(C5,$F$5:$G$9,2,FALSE)
The formula uses the VLOOKUP function which returns the Helper Value as a Rating based on the Performance. Here, we see that ‘Very Good’ is referred to by 4. So the formula will return 4 in cell D5.
- After that, press the ENTER button and you will see the Performance Rating of Jon by a number.
- Thereafter, use the Fill Handle to Autofill the lower cells.
- Next, we will apply Conditional Formatting. Select the cells D5 to D13 and then go to Home >> Conditional Formatting >> New Rule.
Next, the New Formatting Rule window will appear. In this window,
- Select ‘Format all cells based on their values’.
- Next, choose Icon Sets from the Format Style
- After that, check ‘Show Icon only’ for convenience.
- Thereafter, set the Type of values to Number.
- Later, set the condition values for the icons.
- Finally, click OK.
This operation will show you the Rating of the students with 5 different Icons.
You can also find these icons from Home >> Conditional Formatting >> Icon Sets.
Thus you can use Excel Conditional Formatting Icon Sets of more than 3.
Read More: Excel Conditional Formatting: Add Custom Icon Sets (2 Ways)
2. Applying Excel VBA to Use Conditional Formatting with over 3 Icon Sets
We can also use Microsoft Visual Basic for Applications (VBA) to apply Conditional Formatting Icon Sets more than 3 in an Excel sheet. Let’s go through the process below. We will use the dataset that we obtain in Method 1.
Steps:
- First, go to the Developer Tab and then select Visual Basic.
- After that, the VBA editor will appear. Select Insert >> Module to open a VBA Module.
- Now, type the following code in the VBA Module.
Option Explicit
Public Sub ColorIconSet()
Application.ScreenUpdating = False
IconColorSet Sheet3.UsedRange
Application.ScreenUpdating = True
End Sub
Public Sub IconColorSet(ByRef mnRng As Range)
Dim mnCell As Range, mnShape As Shape, mnCell_address As String
For Each mnShape In mnRng.Parent.Shapes
If InStrB(mnShape.Name, "$") > 0 Then mnShape.Delete
Next: DoEvents
For Each mnCell In mnRng
If Not IsError(mnCell.Value2) Then
If Val(mnCell.Value2) > 0 And Not IsDate(mnCell) Then
mnCell_address = mnCell.Address
Set mnShape = Sheet3.Shapes.AddShape(msoShapeOval, mnCell.Left + 5, mnCell.Top + 2, 10, 10)
mnShape.ShapeStyle = msoShapeStylePreset38: mnShape.Name = mnCell_address
mnShape.Fill.ForeColor.RGB = DefineShapeColor(Val(mnCell.Value2))
mnShape.Fill.Solid
End If
End If
Next
End Sub
Public Function DefineShapeColor(ByRef mnCell_Value As Long) As Long
Select Case True
Case mnCell_Value = 1:Â Â DefineShapeColor = RGB(222, 0, 10): Â Exit Function
Case mnCell_Value = 2:Â Â DefineShapeColor = RGB(0, 111, 220):Â Exit Function
Case mnCell_Value = 3:Â Â DefineShapeColor = RGB(0, 0, 255):Â Â Exit Function
Case mnCell_Value = 4:Â Â DefineShapeColor = RGB(210, 0, 200):Â Exit Function
Case mnCell_Value = 5:Â Â DefineShapeColor = RGB(140, 180, 0):Â Exit Function
End Select
End Function
Code Explanation
- First, we named the Sub Procedure as ColorIconSet.
- Then we set the ScreenUpdating Application to False.
- Next, we use the .UsedRange property to use another Sub Procedure IconColorSet.
- In the IconColorSet Sub Procedure, we declared some variables: mnCell As Range, mnShape As Shape and mnCell_address As String.
- After that, a For Loop and If Statement are used to define the position of the Shapes that we are going to use to classify the Performance Rating of the students.
- Also, we made a user-defined function named DefineShapeColor to set the color of the shapes based on the cell values. We will get five different shape colors for five different ratings after running the code. The ratings are defined by numbers from 1 to 5.
- Now, go back to your sheet and Run the Macro.
- After that, you will see the Ratings will have their individual shapes. We obtained these shapes via Conditional Formatting with the help of VBA.
Thus, you can obtain Excel Conditional Formatting Icon Sets of more than 3 by using VBA.
Read More: Excel Conditional Formatting Icon Sets with Relative Reference
Practice Section
Here, I’m giving you the dataset of this article so that you can practice these methods on your own.
Conclusion
Suffice to say, you will learn the easy and effective procedures of using Excel Conditional Formatting Icon Sets more than 3 after reading this article. If you have any better methods or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.
