VBA Selection

What is Excel VBA Selection Property?

The Selection property in Excel VBA refers to the currently selected cells, ranges, or objects within a worksheet. It allows you to perform various operations on the selected elements, such as modifying values, formatting, or extracting data. The VBA Selection property can be a single cell, a range of cells, or even non-contiguous selections.

Let us look at the following image to understand this with a simple example. This code performs the following to the selected cells:

  1. Changes the background color.
  2. Changes font size.
  3. Highlights the cells in a particular color that match the given conditional formatting criteria.
VBA Selection Intro - Code
VBA Selection Intro - Output
Key Takeaways
  1. The Selection property refers to those cells/ranges/objects that are currently selected, and action can be performed on them.
  2. The Selection property can be combined with other VBA statements and functions to automate tasks, perform calculations, and analyze data based on the selected cells or ranges.
  3. Use the Selection.Offset property to dynamically move the selection relative to its current position by specifying the number of rows and columns to offset.
  4. VBA Selection Row property returns the row number of the first cell in the selected range. It allows you to perform row-specific operations, such as modifying cell values, formatting, or applying conditional logic.

How To Use Selection Property in VBA?

To utilize the VBA Selection property effectively in VBA, follow these steps:

Step 1: Activate the appropriate worksheet or object.

VBA selection - use - Step 1

This code activates “Sheet1” within the workbook. Replace “Sheet1” with the name of the desired worksheet or use the appropriate object activation method as per your requirement.x

Step 2: Select the desired cells, ranges, or objects within the worksheet.

VBA selection - use - Step 2

This code selects the range from cell A1 to B5. Next, replace the range with the desired cells, ranges, or objects you want to work with. You can also use other selection methods, like selecting specific rows or columns.

Step 3: Utilize the Selection property to perform operations or make modifications.

VBA selection - use - Step 3

This code changes the background color of the selected range to red using the “Interior.Color” property. Then, replace it with any operation or modification you want to perform on the selected cells, ranges, or objects.

VBA selection - use - Output.jpg

If you see the above image, the code has been used to apply a red highlight to the range of cells from A1 to B5.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.


Let’s explore various examples to illustrate the usage of the VBA Selection property in different scenarios:

Example #1

Suppose we have data in Excel with positive and negative numbers, and we want these numbers to be highlighted in Green and Red for the positive and negative numbers, respectively. Then, we can write the following code to modify the values and formatting of selected cells using the VBA Selection property and applying conditional formatting to cells.

VBA Selection - Example 1

Step 1: Open the Visual Basic Editor (VBE). To open the VBE, press the Alt + F11 keys in Excel or click on the Developer tab in the ribbon and then click the Visual Basic button.

VBA Selection - Example 1 - Step 1

Step 2: Create a new module.

In the VBE, click the Insert menu and select Module to create a new module.

VBA Selection - Example 1 - Step 2

Step 3: Begin the code with by declaring of the subroutine named “ModifySelectedCells” using the Sub keyword.

VBA Selection - Example 1 - Step 3

Step 4: Access the Interior property of the selected cells and set the Color property to red by using the “Selection.Interior.Color = RGB(255, 0, 0)” formulas. Here, “RGB(255, 0, 0)” represents red.

VBA Selection - Example 1 - Step 4

Step 5: Access the Font property of Selection and set the Size property to 12. This code changes the font size of the selected cells.

VBA Selection - Example 1 - Step 5

Step 6: Use “FormatConditions.Add” formula to add a new conditional formatting rule. Here, we specify the Type as “xlCellValue,” Operator as “xlGreater,” and Formula1 as “50”, which means the selected cells will be formatted if their values are greater than 50.

VBA Selection - Example 1 - Step 6

Step 7: Use the “SetFirstPriority method to prioritize the newly added conditional formatting rule among any existing rules for the Selection.

VBA Selection - Example 1 - Step 7

Step 8: Lastly, set the “Interior.Color property of the first “FormatCondition to RGB(0, 255, 0), which represents the color green. It formats the cells that meet the conditional formatting criteria with a green background color.

VBA Selection - Example 1 - Step 8

Step 9: Now save the module and close the VBE. Now select the data you want to apply the conditional formatting to and then press Alt + f8 to open the Macro list then select the “ModifySelectedCells” and run:

VBA Selection - Example 1 - Step 9

Step 10: After you run the code, you will see that the cells are highlighted with RGB colors based on the conditional formatting we applied. The values with positive numbers are highlighted in Green, and the negative one in Red.

VBA Selection - Example 1 - Step 10

Here is the full code:

Sub ModifySelectedCells()
‘ Change background color of selected cells
Selection.Interior.Color = RGB(255, 0, 0)
‘ Change font size of selected cells
Selection.Font.Size = 12
‘ Apply conditional formatting to selected cells
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:=”50″
Selection.FormatConditions(1).Interior.Color = RGB(0, 255, 0)
End Sub

Example #2

Let us see how the VBA Selection Copy can copy selected ranges to different locations within the same worksheet or other worksheets. For example, refer to “Sheet 2” in the workbook “VBA Selection.”

Step 1: In the new VBA module, begin the code by declaring the subroutine named “CopySelectedRanges” using the Sub keyword.

Example 2 - Step 1

Step 2: Use the “Selection.Copy” method to copy the selected ranges, and then specify the destination as Range(“A1”), which represents cell A1 in the same worksheet. This line of code copies the selected ranges in VBA to another location within the same worksheet.

Example 2 - Step 2

Step 3: Activate the desired worksheet using the “Sheets(“Sheet2”).Activate” formula. Then, we use the “Selection.Copy” formula to copy the selected ranges and specify the destination as “Sheets(“Sheet2”).Range(“B2”),” which represents cell B2 in the “Sheet2” worksheet.

Example 2 - Step 3

Step 4: Now save the module and close the VBE and then press Alt + f8 to open the Macro list and select “CopySelectedRanges” and run it.

Example 2 - Step 4

Step 5: After running this VBA code, the selected ranges will be copied to the specified destinations; here, it is cell B2.

Example 2 - Step 5

Here is the full code:

Sub CopySelectedRanges()
‘ Copy selected ranges to another location within the worksheet
Selection.Copy Destination:=Range(“A1”)
‘ Copy selected ranges to a different worksheet
Selection.Copy Destination:=Sheets(“Sheet2”).Range(“B2”)
End Sub

Example #3

Now let us look at the VBA Selection Offset property that allows you to move the Selection relative to its current position dynamically. For example: When selecting Cell A1 and using the Selection Offset property, the cell will be moved to a specified location, displaying “Modified Value.”

Example 3

Refer to “Sheet 1” in the “VBA Selection Offset” workbook.

Step 1: In the new VBA module, begin the code with the declaration of the subroutine named “ManipulateSelectionUsingOffset” using the Sub keyword.

Example 3 - Step 1

Step 2: Use the “Selection.Offset” property to create a new range object named “offsetRange.” We specify the offset as 1 row down and two columns to the right relative to the current Selection. It means the new range will be shifted one row below and two columns to the right of the selected range.

Example 3 - Step 2

Step 3: Now manipulate the “offsetRange” based on its new position. In this example, we modify the value and font properties of the offset range.

The line “offsetRange.Value = “Modified Value” assigns the value “Modified Value” to the cells within the offset range.

The line “offsetRange.Font.Bold” = True sets the font style of the offset range to bold.

Example 3 - Step 3

Step 4: Save the module and close the VBE. Now, open the macro list using Alt + F8 and run “ManipulateSelectionUsingOffset.”

Example 3 - Step 4

Step 5: After running this VBA code, the selected range will be offset by 1 row down and 2 columns to the right, and the specified modifications will be applied to the offset range.

Example 3 - Step 5

Here is the full code:

Sub ManipulateSelectionUsingOffset()
‘ Offset the selection by rows and columns
Dim offsetRange As Range
Set offsetRange = Selection.Offset(1, 2)
‘ Manipulate the offset range
offsetRange.Value = “Modified Value”
offsetRange.Font.Bold = True
End Sub

Important Things To Note

When working with the Selection property in VBA, it’s essential to keep the following points in mind to unlock its full potential:

  1. Don’t forget to clear the Selection when needed. Using the code “Application.CutCopyMode = False,” you can remove the Selection and eliminate those distracting marching ants (dotted selection lines in Excel), ensuring a clean slate for your next action.
  2. The Selection property allows you to access the range of selected cells or ranges. Assigning it to a variable, such as “Set rng = Selection,” you can effortlessly manipulate the selected range, apply to format, perform calculations, and more, opening endless possibilities.
  3. With a single command, you can apply an “auto-filter” to the selected range, enabling you to slice and dice your data based on specific criteria.\
  4. Use the “Selection.Offset” property to shift the Selection’s position dynamically. Specifying the desired number of rows and columns to offset allows you to explore new perspectives, adapt to changing data, and precisely navigate your worksheet.

Frequently Asked Questions (FAQs)

1. How do I clear the selection in Excel VBA?

To clear the selection in VBA, use the code snippet “Application.CutCopyMode = False.” This line of code clears the selection and removes the marching ants (animated dotted lines) around the selected cells.
Application.CutCopyMode = False

2. How can I obtain the range of the selection in VBA?

To obtain the VBA Selection Range, you can assign it to a variable using the Selection property.
Dim rng As Range
Set rng = Selection

Now, the variable “rng” holds the range of the selected cells or ranges, and you can perform further operations on it.

3. What is VBA Selection Row?

“Selection.Row” property is used to retrieve the row number of the first cell in a selected range or active cell. It allows you to programmatically determine the row number and perform operations specific to that row.

selectedRow = Selection.Row

The variable “selectedRow” is declared a Long data type in this code. Assigning Selection.Row to the variable captures the row number of the first cell in the selected range or active cell..

4. What is Selection.Offset in VBA?

VBA Selection Offset is a property that moves the selection’s position relative to its current location by specifying the number of rows and columns to offset.
Selection.Offset(1, 0).Select

Download Template

This article must be helpful to understand the VBA Selection, with its formula and examples. You can download the template here to use it instantly.

This has been a guide to VBA Selection. Here we learn how to use Selection Property along with examples and downloadable template. You may learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *