Excel VBA Select Case Range of Cells in 8 Steps (+ Workbook Example)

In this Excel VBA Select Case Range of Cells Tutorial, you learn how to create an Excel VBA Select Case range of cells statement to:

Repeatedly execute a set of statements (including a nested Select Case statement) for each cell inside a range of cells; and
(On each loop iteration) Conditionally execute a set of statements based on an expression's value.

This Excel VBA Select Case Range of Cells Tutorial is accompanied by an Excel workbook with the data and VBA code I use. Get this example workbook (for free) by clicking the button below.

The VBA code in the Excel workbook that accompanies this Excel VBA Select Case Range of Cells Tutorial is (always) stored in the Visual Basic Editor (VBE). If you don't know how to work with the VBE, I suggest you read my Visual Basic Editor (VBE) Tutorial. I link to this Tutorial in the Related Excel Macro and VBA Training Materials and Resources Section.

Table of Contents

Related Excel Macro and VBA Training Materials and Resources

The following Excel Macro and VBA Tutorials may help you better understand and implement this VBA Select Case Range of Cells Tutorial.

Tutorials about general macro and VBA constructs and structures:
- Tutorials for Beginners:
  - Excel Macros: Click here to open.
  - Excel VBA: Click here to open.
- Enable macros in Excel: Click here to open.
- Work with the Visual Basic Editor (VBE): Click here to open.
- Create Sub procedures: Click here to open.
- Work with:
  - Objects (click here to open), including cell ranges (click here to open).
  - Properties: Click here to open.
  - Variables: Click here to open.
  - Data types: Click here to open.
  - Loops: Click here to open.
Tutorials with practical VBA applications and macro examples:
- Find the last row or column: Click here to open.
- Set or get a cell's or cell range's value: Click here to open.
- Clear cells: Click here to open.
- Create named ranges: Click here to open.
- Merge or unmerge cells: Click here to open.
- Insert rows: Click here to open.
- Delete columns: Click here to open.
- Check if cell range is empty: Click here to open.
- Delete rows: Click here to open.
- Delete blank or empty rows: Click here to open.
- MsgBox: Click here to open.

This Excel VBA Select Case Range of Cells Tutorial is part of a more comprehensive series of Excel VBA Select Case Tutorials.

Excel VBA Select Case Tutorial: Click here to open.
Excel VBA Select Case Or: Click here to open.
Excel VBA Select Case And Operator: Click here to open.
Excel VBA Select Case Multiple Test Expressions: Click here to open.
Excel VBA Select Case Like Wildcard: Click here to open.
Excel VBA Select Case Inside For… Next Loop: Click here to open.
Excel VBA Select Case Range of Cells: Click here to open.

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials and Training Resources:

Excel Keyboard Shortcuts Cheat Sheet: Click here to open.
Work with the Excel XLOOKUP Function: Click here to open.
Excel Power Query (Get & Transform) Tutorial for Beginners: Click here to open.

If you want to learn how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

Premium Courses at the Power Spreadsheets Academy: Click here to open.
Books at the Power Spreadsheets Library: Click here to open.
VBA Cheat Sheets: Click here to open.

If you want to save time when working with macros and VBA, you may be interested in AutoMacro: Click here to learn more about AutoMacro (affiliate link). AutoMacro is an add-in for VBA that installs directly into the VBE. Depending on the version, AutoMacro comes loaded with:

Code generators.
An extensive code library.
The ability to create your own code library.
Advanced coding tools.

If you need help with Excel tasks/projects, you may be interested in working with me: Click here to learn more about working with me.

Excel VBA Select Case Range of Cells Snippet Template/Structure

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/select-case-cell-range/
For Each RangeObjectVariable In CellRangeObject
    Select Case TestExpression
        Case CaseExpression1
            CaseStatements1
        Case CaseExpression2
            CaseStatements2
        '...
        Case CaseExpression#
            CaseStatements#
        Case Else
            ElseStatements
    End Select
Next RangeObjectVariable

Step-by-Step Process to Create an Excel VBA Select Case Range of Cells Statement

(1) Enter the opening and closing statements of the For Each… Next loop:

For Each.
Next.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/select-case-cell-range/
For Each '...
    '...
Next '...

(2) Refer to the loop iteration object variable in (both) the opening and closing statements of the For Each… Next loop.

As a general rule: The loop iteration object variable in a Select Case range of cells statements must be of the Range object data type.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/select-case-cell-range/
For Each RangeObjectVariable In '...
    '...
Next RangeObjectVariable

(3) Refer to the Range object representing the range of cells the For Each… Next loop works with, in the opening statement of the For Each… Next loop.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/select-case-cell-range/
For Each RangeObjectVariable In CellRangeObject
    '...
Next RangeObjectVariable

(4) Enter the opening and closing statements of the Select Case statement:

Select Case.
End Select.

To create an Excel VBA Select Case range of cells statement:

Nest this Select Case statement;
Inside the For Each… Next loop (you created in steps #1 to #3).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/select-case-cell-range/
For Each RangeObjectVariable In CellRangeObject
    Select Case '...
        '...
    End Select
Next RangeObjectVariable

(5) Specify the test expression VBA uses to identify the set of statements to execute.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/select-case-cell-range/
For Each RangeObjectVariable In CellRangeObject
    Select Case TestExpression
        '...
    End Select
Next RangeObjectVariable

(6) Specify the case expressions used by VBA to identify the set of statements to execute. Each case expression is preceded by the Case keyword.

Case CaseExpression1.
Case CaseExpression2.
…
Case CaseExpression#.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/select-case-cell-range/
For Each RangeObjectVariable In CellRangeObject
    Select Case TestExpression
        Case CaseExpression1
            '...
        Case CaseExpression2
            '...
        '...
        Case CaseExpression#
            '...
        '...
    End Select
Next RangeObjectVariable

(7) Specify the set of statements to execute when the applicable case expression (you specified in step #6) matches the test expression (you specified in step #5).

CaseStatements1.
CaseStatements2.
…
CaseStatements#.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/select-case-cell-range/
For Each RangeObjectVariable In CellRangeObject
    Select Case TestExpression
        Case CaseExpression1
            CaseStatements1
        Case CaseExpression2
            CaseStatements2
        '...
        Case CaseExpression#
            CaseStatements#
        '...
    End Select
Next RangeObjectVariable

(8) Specify the set of statements to execute if no case expression (you specified in step #6) matches the test expression (you specified in step #5). These catch-all statements:

Follow the Case Else keyword; and
Are optional.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/select-case-cell-range/
For Each RangeObjectVariable In CellRangeObject
    Select Case TestExpression
        Case CaseExpression1
            CaseStatements1
        Case CaseExpression2
            CaseStatements2
        '...
        Case CaseExpression#
            CaseStatements#
        Case Else
            ElseStatements
    End Select
Next RangeObjectVariable

How (and Why) the VBA Select Case Range of Cells Statement Works

The For Each… Next loop repeatedly executes a set of statements for each:

Element in an array (excluding arrays of user-defined types); or
Object in a collection.

You can use the For Each… Next loop to work (individually) with each cell inside a range of cells. In such cases, the number of loop iterations is determined by the number of cells in the applicable range of cells.

The Select Case statement does the following:

Compare a test expression to several case expressions.
Determine how to proceed based on the case expression that matches the test expression.

You can nest the Select Case statement inside a For Each… Next loop that loops through all cells in a range of cells. In such cases:

The Select Case statement is repeatedly executed for each cell inside the range of cells.
When the test expression matches an individual case expression, the Select Case statement:
- Executes the set of statements associated to the applicable Case clause; and
- Exits the Select Case statement (but not the For Each… Next loop).

A nested Select Case statement must be a stand-alone unit. In other words: A nested Select Case statement must:

Be complete; and
Comply with the applicable syntax/structure requirements.

Excel VBA Select Case Range of Cells Example Macro

The VBA Select Case range of cells example macro (below) does the following:

Loop through all cells in the current selection. The VBA Select Case range of cells example macro assumes the current selection is a range of cells.
Display a message box (for each cell).

The message inside the message box varies, depending on the value stored inside the cell the loop is currently iterating through. I use a VBA Select Case range of cells statement to specify the proper message.

Sub SelectCaseCellRange()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/select-case-cell-range/
    
    'Declare:
        'Iteration object variable
        'Variable to represent message to be displayed in message box
    Dim iCell As Range
    Dim MyMsgBoxMessage As String
    
    'Loop through all cells in the current selection
    For Each iCell In Selection
        
        'Assign a string to the MyMsgBoxMessage variable depending on the value stored in the cell the loop is currently iterating through
        Select Case iCell.Value
            Case 1 To 3: MyMsgBoxMessage = "Value stored in current cell is from 1 to 3"
            Case 4 To 6: MyMsgBoxMessage = "Value stored in current cell is from 4 to 6"
            Case 7 To 9: MyMsgBoxMessage = "Value stored in current cell is from 7 to 9"
            Case Else: MyMsgBoxMessage = "Value stored in current cell is not from 1 to 9"
        End Select
        
        'Display message box with string held by the MyMsgBoxMessage variable
        MsgBox MyMsgBoxMessage
    
    Next iCell

End Sub

The VBA Select Case range of cells statement inside the VBA Select Case range of cells example macro specifies 4 possible messages:

If the value stored in the cell the loop is currently iterating through is from 1 to 3 (iCell.Value = 1 to iCell.Value = 3): “Value stored in current cell is from 1 to 3”.
If the value stored in the cell the loop is currently iterating through is from 4 to 6 (iCell.Value = 4 to iCell.Value = 6): “Value stored in current cell is from 4 to 6”.
If the value stored in the cell the loop is currently iterating through is from 7 to 9 (iCell.Value = 7 to iCell.Value = 9): “Value stored in current cell is from 7 to 9”.
If the value stored in the cell the loop is currently iterating through is not from 1 to 9 (for example, iCell.Value = 10): “Value stored in current cell not from 1 to 9”.

The GIF below displays the message boxes shown by Excel when I:

Select a range of cells (10 cells) with integers between 1 and 10 (9, 3, 7, 1, 1, 6, 8, 4, 10, 7); and
Execute the Excel VBA Select Case range of cells example macro.

Excel VBA Select Case range of cells example macro results

Download the Excel VBA Select Case Range of Cells Example Workbook