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.

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.

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.

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.

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.

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.

Examples

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.

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.

Step 2: Create a new module.

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

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

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.

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.

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.

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

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.

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:

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.

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(Selection.FormatConditions.Count).SetFirstPriority
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.

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.

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.

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.

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

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
Sheets(“Sheet2”).Activate
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.”

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.

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.

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.

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

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.

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)

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.

Recommended Articles

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 –