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

Get FREE Advanced Excel Exercises with Solutions!

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.

Opening VBA window from the Developer tab

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.

Opening new code module in the VBA window

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

Preparing code window for event


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.

Excel VBA Code to show cell address when selection in target range

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

Code Breakdown:

  • Firstly, we declared a private sub-procedure named Worksheet_SelectionChange. You can select it from the upper right corner of the worksheet code module. Target is a built-in variable of range type for this private sub.
  • Next, we checked if the Target (currently selected cell) is within the range B5:D11 or not. We used the Intersect function of VBA for the task.
  • If the target is within our defined range then, we used MsgBox to display the cell address of the selected cell.

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 to highlight row when changing cells in target range

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

Code Breakdown:

  • Option Explicit at the beginning means we have to declare every variable before using them in the code.
  • We checked whether our Target (currently selected cell) is within range B5:B11.
  • Then, with Cells(1,1) and such we navigated the cells beside Target and by setting ColorIndex property to 15, we filled those cells with color.

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

Highlighted row for change in target range

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 to insert data from another range for double-clicking on 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

Code Breakdown:

  • Firstly, we declare a private sub-procedure for double-click.
  • We check if the double-clicked Target is within range B5:D11.
  • If it’s true, then, we set a value for the Target The value is taken from another cell that we located with the help of Offset property.

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.

Inserted data from another range for double-clicking on target range

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 to change the font for selecting cell

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.

Changed font for selection within target range

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 to use single cell as target range

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

Code Breakdown:

  • We checked if the Target (Selected cell) is B8 or not. We checked with the help of the Intersect Instead, you can also use “Target.Address = B8” condition with If statement for the same task.
  • If that is true then, we have navigated the Target cell and the cells beside it with the Cells property and have set the ColorIndex property to 20. Which highlights those cells with color.
Chanigng row fill color for change in single cell as target range

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.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Mehedi Hasan Shimul
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo