Select Case Statement in Microsoft Office Excel 2013 using VBA
By using if condition we can check only one condition at a time and if you want to check multiple conditions we have to use multiple if conditions. Instead of if condition we can use Select Case statement when we have multiple conditions in VBA.
Open Microsoft Office 2013 Excel => Add command button and some colors to first column as shown in below image.
On the button click event write below code which using Select Case statement.
Private Sub CommandButton1_Click()
finalRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To finalRow
Select Case Cells(i, 1).Value
Case "Red"
Cells(i, 1).Interior.Color = RGB(255, 0, 0)
Case "Green"
Cells(i, 1).Interior.Color = RGB(0, 255, 0)
Case "Blue"
Cells(i, 1).Interior.Color = RGB(0, 0, 255)
Case "Brown"
Cells(i, 1).Interior.Color = RGB(165, 42, 42)
Case "Yellow"
Cells(i, 1).Interior.Color = RGB(255, 255, 0)
Case "Pink"
Cells(i, 1).Interior.Color = RGB(255, 192, 203)
Case Else
Cells(i, 1).Interior.Color = RGB(0, 0, 0)
End Select
Next i
End Sub
As shown above we are using Select Case statement and based on cell value change the cell background color.
Now click the button, it changes the cells background color as shown below.
We can have multiple values in each case statement as shown below.
Private Sub CommandButton1_Click()
finalRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To finalRow
Select Case Cells(i, 1).Value
Case "Red", "Green", "Blue"
Cells(i, 1).Interior.Color = RGB(255, 0, 0)
Case "Brown"
Cells(i, 1).Interior.Color = RGB(165, 42, 42)
Case "Yellow"
Cells(i, 1).Interior.Color = RGB(255, 255, 0)
Case "Pink"
Cells(i, 1).Interior.Color = RGB(255, 192, 203)
Case Else
Cells(i, 1).Interior.Color = RGB(0, 0, 0)
End Select
Next i
End Sub
In the above code, we are changing the cell background color to Red if the cell values are "Red" or "Green"or "Blue".
