Working with Select Case Statement

Select case statement is more readable compare with If Then Else statement if the condition to many level for example below table:

No.

Condition

Grade

Bg Color

1

Score More Than >80

A

Green

2

Score More Than >60

B

Yellow

3

Score More Than >40

C

Blue

4

Other Score

D

Red

Base on the above table we have 4 conditions to test and give different result into below table.

VBA Code:

Option Explicit
Sub SelectCaseExample()

    Dim i As Integer
    Dim IntScore As Integer
    With ActiveSheet
        i = 2
        Do
            IntScore = .Range("C" & i)
            Select Case IntScore
                Case Is > 80
                    .Range("D" & i) = "A"
                    .Range("D" & i).Interior.ColorIndex = 4
                Case Is > 60
                    .Range("D" & i) = "B"
                    .Range("D" & i).Interior.ColorIndex = 6
                Case Is > 40
                    .Range("D" & i) = "C"
                    .Range("D" & i).Interior.ColorIndex = 5
                Case Else
                    .Range("D" & i) = "D"
                    .Range("D" & i).Interior.ColorIndex = 3
            End Select
            i = i + 1
        Loop While .Range("A" & i) <> ""
    End With

End Sub

Result after run above code.

Microsoft Reference - Select Case Statement

Practice makes perfect. Thank You.