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 SubWe 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 SubWe 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 SubWe 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 SubWe 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 SubWe 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 SubWe 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
I am working with your Practice sheet but the selectrange1 macro gives a Run-time error ‘1004’ Select method of Range class failed. Please advise.
Hi David, I think maybe you have forgotten to change the name of the worksheet from another to practice while working with the practice worksheet. So, you can try out the following code to work with the practice sheet.
Sub selectrange1()
Dim LR As Long
Dim x1 As Range, y1 As Range
With ThisWorkbook.Worksheets(“practice”)
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