How to Select Range Based on Cell Value VBA (7 Ways)

Method 1 – Selecting Range Based On Another Cell Value

We will select the cell range in the Region and Sales column based on the string Apple in the Product column.

Step 1:
➤Go to Developer Tab>>Visual Basic

The Visual Basic Editor will open up.
➤Go to Insert Tab>> Module Option

A Module will be created.

Step 2:
➤Enter the following code

Sub selectrange1()

Dim LR As Long

Dim x1 As Range, y1 As Range

With ThisWorkbook.Worksheets("another")

    LR = Cells(Rows.Count, "B").End(xlUp).Row

    Application.ScreenUpdating = False

    For Each x1 In .Range("B1:B" & LR)

        If x1.Text = "Apple" Then

            If y1 Is Nothing Then

            Set y1 = .Range("C" & x1.Row).Resize(, 2)

            Else

            Set y1 = Union(y1, .Range("C" & x1.Row).Resize(, 2))

            End If

        End If

        Next x1

        Application.ScreenUpdating = True

    End With

    If Not y1 Is Nothing Then y1.Select

End Sub

We declared LR, and x1 and y1 as Long and Range respectively.

“another” is the sheet name and the With statement lets you specify an object or user-defined type once for an entire series of statements.

LR will give you the last row of your data table and the VBA For-Next loop is used for performing the actions in the range of Column B.

We have used two IF loops, one is for checking the string “Apple” in Column B and the other is for storing the range between Column C and Column D corresponding to the cells having “Apple” in a variable y1.

Range(“C” & x1.Row).Resize(, 2) indicates that selecting two columns from Column C and Union will return the union of multiple ranges corresponding to the cell value “Apple” and the ranges will be selected.

➤Press F5

Result:
It will select the range of cells in the Region and Sales column corresponding to the cell value Apple in the Product column.

Method 2 – Changing Color of a Cell Range Based On Another Cell Value

Step 1:
➤Follow Step-01 of Method-1
➤Enter the following code

Sub selectrange2()

Dim Rng As Range

Dim Lr As Long

Dim n As Long

Lr = Cells(Rows.Count, "B").End(xlUp).Row

    For n = 1 To Lr

        If Cells(n, "B").Value = "Apple" Then

            If Rng Is Nothing Then

            Set Rng = Cells(n, "D")

            Else

            Set Rng = Union(Rng, Cells(n, "D"))

            End If

        End If

    Next n

Rng.Interior.Color = vbRed

End Sub

We declared Lr and n, and Rng as Long and Range respectively.
Lr will give you the last row of your data table and the FOR loop is used for performing the actions for rows from 1 To Lr.

We have used two IF loops, one is for checking the string “Apple” in Column B for rows 1 To Lr and the other is for storing the range of cells in Column D corresponding to the cells having “Apple” in a variable Rng. Union will return the union of multiple ranges corresponding to the cell value “Apple” and the ranges will be colored red.

➤Press F5

Result:
It will change the color of the range of cells in the Sales column corresponding to the cell value Apple in the Product column.

Method 3 – Selecting Range Based On Same Cell Value

Step 1:
➤Follow Step-01 of Method-1
➤Enter the following code

Sub selectrange3()

Dim Rng As Range

Dim Lr As Long

Dim n As Long

Lr = Cells(Rows.Count, "B").End(xlUp).Row

For n = 1 To Lr

    If Cells(n, "B").Value = "Orange" Then

        If Rng Is Nothing Then

        Set Rng = Cells(n, "B")

        Else

        Set Rng = Union(Rng, Cells(n, "B"))

        End If

    End If

    Next n

Rng.Select

End Sub

We declared Lr, n, and Rng as Long and Range respectively.
Lr will give you the last row of your data table and the FOR loop is used for performing the actions for rows from 1 To Lr.

We have used two IF loops, one is for checking the string “Orange” in Column B for rows 1 To Lr and the other is for storing the range of cells in Column B corresponding to the cells having “Orange” in a variable Rng. Union will return the union of multiple ranges corresponding to the cell value “Orange” and the ranges will be selected.

➤Press F5

Result:
It will select the range of cells in the Product column for the cell value Orange in this column.

Method 4 – Selecting Range Based On Multiple Criteria

Let’s select the cells in the Sales column that have values of more than $1500.00 and less than $2000.00.

Step 1:
➤Follow Step-01 of Method-1
➤Enter the following code

Sub selectrange4(Rng1 As Range, MinValue As Double, MaxValue As Double)

    Dim rng As Range

    Dim Cell As Object

    For Each Cell In Rng1

        If Cell.Value >= MinValue And Cell.Value <= MaxValue Then

            If rng Is Nothing Then

                Set rng = Range(Cell.Address)

            Else

                Set rng = Union(rng, Range(Cell.Address))

            End If

        End If

    Next

    rng.select

End Sub

Sub Callselectrange4()

     Call selectrange4(Range("D5:D12"), 1500, 2000)

End Sub

We declared Rng1, rng, Minvalue, Maxvalue, and Cell as Range, Double, and Object respectively.

We used two Sub procedures, the one Callselectrange4() is for calling the other Sub procedure selectrange4 and giving the value of the variables Rng1 as Range(“D5:D12”), Minvalue as 1500, and Maxvalue as 2000 respectively.

One FOR loop and two IF loops have been used in the selectrange4 Sub procedure.

➤Press F5

Result:
It will select the range of cells in the Sales column for the cells that have values of more than $1500.00 and less than $2000.00.

Method 5 – Changing Font Style of a Range Based On Multiple Criteria

Let’s bold the fonts of the cells in the Sales column which have values of more than $1500.00 and less than $2000.00.

Step 1:
➤Follow Step-01 of Method-1
➤Enter the following code

Sub selectrange5(Rng1 As Range, MinValue As Double, MaxValue As Double)

    Dim rng As Range

    Dim Cell As Object

    For Each Cell In Rng1

        If Cell.Value >= MinValue And Cell.Value <= MaxValue Then

            If rng Is Nothing Then

                Set rng = Range(Cell.Address)

            Else

                Set rng = Union(rng, Range(Cell.Address))

            End If

        End If

    Next

    rng.Font.Bold=True

End Sub

Sub Callselectrange5()

     Call selectrange5(Range("D5:D12"), 1500, 2000)

End Sub

We declared Rng1, rng, Minvalue, Maxvalue, and Cell as Range, Double, and Object respectively.

We used two Sub procedures, the one Callselectrange4() is for calling the other Sub procedure selectrange4 and giving the value of the variables Rng1 as Range(“D5:D12”), Minvalue as 1500, and Maxvalue as 2000 respectively.

One FOR loop and two IF loops have been used in the selectrange4 Sub procedure.

➤Press F5

Result:
It will bold the fonts of the range of cells in the Sales column for the cells that have values of more than $1500.00 and less than $2000.00.

Method 6 – Selecting Range Based On Blank Cells

We have some blank cells (I have removed the values for explaining this method) in the Sales column and to select these blank cells we will use the VBA code of this method.

Step 1:
➤Follow Step-01 of Method-1
➤Enter the following code

Sub selectrange6()

Dim Rng As Range

Dim Lr As Long

Dim n As Long

Lr = Cells(Rows.Count, "D").End(xlUp).Row

    For n = 5 To Lr

        If Cells(n, "D").Value = "" Then

            If Rng Is Nothing Then

            Set Rng = Cells(n, "D")

            Else

            Set Rng = Union(Rng, Cells(n, "D"))

            End If

        End If

    Next n

Rng.Select

End Sub

∑e declared Lr, n, and Rng as Long and Range respectively.

Lr will give you the last row of your data table and the FOR loop is used for performing the actions for rows from 5 To Lr. 5 is for the first row of our range.

We have used two IF loops, one is for checking the string “” (Blank) in Column D for rows 1 To Lr and the other is for storing the range of cells in Column D corresponding to the cells having Blank in a variable Rng. Union will return the union of multiple ranges corresponding to the blank cells, and the ranges will be selected.

➤Press F5

Result:
It will select the blank cells in the column Sales.

Method 7 – Changing Cell Value Based On Selected Cell

Step 1:
➤Follow Step-01 of Method-1
➤Enter the following code

Sub selectrange7(ByVal target As Range)

If Selection.Count = 1 Then

    If Not Intersect(target, Range("B5:B12")) Is Nothing Then

        Call ShowBcolumn

    Else

        Call ShowActive

    End If

End If

End Sub

Sub ShowBcolumn()

Range("E5").Value = ActiveCell.Value

End Sub

Sub ShowActive()

Range("E5").Value = Range("B5").Value

End Sub

We used three Sub procedures, selectrange7 is the main procedure which will call the other two Sub procedures ShowBcolumn() and ShowActive().

Intersect will give the range that is the intersection of two or more ranges and when the selected cell in the dataset is intersected with the range “B5:B12” then it will call ShowBcolumn(). It will return the selected cell’s value in the E5 cell. Otherwise, it will call ShowActive() which will return the value of cell B5.

➤Save the code.
➤Select any cell in the Product column (I have selected the cell containing Banana)
➤Go to Developer Tab>>Macros Option

The Macro Wizard will open.
➤Select the Macro name ShowBcolumn and Run.

Result:
It will output the value of the selected cell (Banana) in cell E5.

Read More: Excel VBA: Select Range with Offset Based on Active Cell

Download Workbook

Related Articles

• Excel VBA to Select Used Range in Column
• Excel VBA to Cancel Selection
• How to Use VBA to Select Range from Active Cell in Excel