How to Use Excel VBA Target Range (4 Suitable Examples)

When we work in Excel, sometimes we need to run events when selecting a cell from a specific range that is known as the target address. It’s quite simple to perform different actions when we select or change the data of cells from using Excel VBA target range. This article will show how to use Excel VBA Target Range with different examples.

As we know, almost every task in Excel can be done with the help of Visual Basics for Application (VBA). The events related to target range selection or change can be easily done with the help of VBA.

In this article, we will demonstrate performing simple tasks like changing the font, filling the cell color or inputting data from another location, etc. when we select or change anything in cells from the target range.

What Is Target Range in Excel VBA?

The Target in Excel VBA refers to the object that was last selected or clicked on by the user. The Target Range in Excel VBA refers to the cell or range of cells that the user has selected or clicked on.

When writing VBA code for an event procedure such as “Worksheet_SelectionChange“, the Target Range can be used to perform actions based on the selected cell or range of cells. For example, if the user selects a range of cells, the VBA code can calculate the sum of the selected cells or change the formatting of the selected cells.

How to Launch VBA Editor in Excel

For inserting any VBA code, open the VBA window first. For that, just click on the Developer tab and select Visual Basic from the appeared options. Also, you can open the VBA window by pressing the keyboard shortcut Alt + F11.

If your Excel doesn’t have the Developer tab automatically, then you can enable Developer tab in Excel first.

After opening the VBA window, you need to double-click on the worksheet name on which you want to run the event.

Later, select Worksheet from the upper left list and event name from the upper right list of the code module.

Excel VBA Target Range: 4 Suitable Examples

Without any further delay, now we will discuss the use of the Target Range with VBA. In our examples, we will initiate different actions on the worksheet depending on selection, change, or double-click on the cells from the Target Range.

1. Showing Cell Address with MsgBox for Changing Selection in Target Range

In the first example, we will discuss the procedures to show the cell address with VBA MsgBox when we select any cell from a predefined Target Range.

For that, use the attached code in the respective worksheet code module. Then select any cell from the Target Range from the corresponding worksheet.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Firstly, we are defining the target range
If Not Intersect(Target, Range("B5:D11")) Is Nothing Then
'Now let's do something for target range selection
MsgBox "The address of your selection is: " & Target.Address
End If
End Sub

Follow the video for better clarification of the example.

Read More: How to Use Target Address in Excel VBA

2. Highlight Row for Any Change in Multiple Cells Within Target Range

Now, let’s highlight multiple cells when we bring any change in the cells from Target Range. Change can be done with the data stored in the cell or changing cell property etc.

Simply, insert the following attached code in the worksheet code module. Then, change the cell property of cells from the Target Range in the corresponding worksheet.

Code:

Option Explicit
'Excel worksheet change event Range B5 to B11
Private Sub Worksheet_Change(ByVal Target As Range)
'Identifying if the change is in target range
If Not Intersect(Target, Range("B5:B11")) Is Nothing Then
'Changing Cell fill color for change in target range
Target.Cells(1, 1).Interior.ColorIndex = 15
Target.Cells(1, 2).Interior.ColorIndex = 15
Target.Cells(1, 3).Interior.ColorIndex = 15
End If
End Sub

After including the code and bringing a change in the target range you will find the corresponding cells colored like in the attached image.

Read More: Excel VBA: How to Use Target Row

3. Inserting Data from Another Range by Double Clicking Within Target Range in Excel

Now, we will double-click on cells in Target Range and the cell will be filled with data from another range of the worksheet.

Apply the attached code in the corresponding worksheet code module. Then, double-click on the cells within Target Range.

Code:

'Excel worksheet double click change event Range B5 to D11
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Identifying if the double click is on target range
If Not Intersect(Target, Range("B5:D11")) Is Nothing Then
'taking data from another cell for double clicking
Target.Value = ActiveCell.Offset(10, 0).Value
End If
End Sub

After inserting the code, if you double-click on the target range you will see the cell is filled with data from another cell just like in the following image.

Read More: How to Use Target Value in Excel VBA

4. Changing Font After Selecting Any Cell in Target Range

This example will demonstrate the procedures to change the font (to bold) of cells from the Target Range when selected.

All you need to do is insert the following code in the respective worksheet code module. Then, just select cells from Target Range.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Identifying if the change in selection is occurring inside target range
If Not Intersect(Target, Range("B5:B11")) Is Nothing Then
'Changing font for selection change inside target range
Range(Target.Address).Font.Bold = True
End If
End Sub

In the code, we checked if the Target (selected cell) is within range B5:B11. If that’s true then, we set Font.Bold property for that selected cell to True.

Read More: Excel VBA Target Cell

How to Use a Single Cell as Target Address in Excel VBA

So far, we have discussed the use of the Target Range. Now, let’s see the simpler version of that. We can set a single cell as Target Address. In this example, we will set cell B8 as Target, and selecting this cell will cause changes in the fill color of cells B8, C8, and D8.

Just apply the attached code in the corresponding worksheet code module. Afterward, select cell B8, done.

Code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Checking if the change in target cell
If Not Intersect(Target, Range("B8")) Is Nothing Then
'Performing color fill for cells for change in target cell
Target.Cells(1, 1).Interior.ColorIndex = 20
Target.Cells(1, 2).Interior.ColorIndex = 20
Target.Cells(1, 3).Interior.ColorIndex = 20
End If
End Sub

In this example, we changed the value of cell B8 which initiates the color fill-up of cells B8, C8, and D8 just like in the given image.

Wrapping Up

• We discussed Target Range and explained its use with 4 suitable examples.
• In the first example, we showed the cell address of the target range with MsgBox.
• The second example highlights the cells when we bring any change in the target range.
• Then, we showed an example to include data to the target range for double clicking on it.
• Later, the example illustrates the font change for the selection of the target range.
• In addition, we showed to use of a single cell as the target address.

Things to Remember

• Don’t forget to save the workbook as the xlsm file after writing the VBA code.
• Deal carefully with cell references. In a few cases, we used absolute cell references.
• Don’t insert the code for the event running in the normal module. You have to insert it in the worksheet code module.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

Hope you have understood the concept of Excel VBA Target Range. We can utilize a target range for initiating different events as we explained with the examples in the article. Hopefully, you will be able to use the target range afterward. Don’t hesitate to comment if you have any suggestions or queries.