How to Use Target Address in Excel VBA (3 Examples)

The sample dataset contains the production and selling prices of different mobile phones. We are going to use Target.Address to change cell formatting when the budget is changed by the user.

Method 1 – Changing Cell Format According To Budget

Go to Developer, select Visual Basics and then Microsoft Excel objects (Sheets, in this case).
Select the corresponding worksheet and enter the following code.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    If Target.Address = "$H$5" Then
    For Each cell In Range("C5:C12")
        If cell.Offset(0, 2).Value <= Target.Value Then
            cell.Interior.Color = RGB(144, 238, 144)
        Else
            cell.Interior.ColorIndex = xlNone
        End If
    Next cell
    End If
End Sub

By changing the “My Budget to Buy Phone” value (in cell H5) we get the following result.

Code Explanation

Private Sub Worksheet_Change(ByVal Target As Range)

This line defines the event handler that will be triggered when a cell on the worksheet changes.

Dim cell As Range

This line declares a Range variable named “cell”. We will use it later in the code to loop through a range of cells.

If Target.Address = "$H$5" Then

This line sets up a condition that checks if the cell that was changed (as specified by the “Target” parameter) is cell H5. If the condition is true, the code within the “If” block will be executed.

For Each cell In Range("C5:C12")

This line sets up a loop that will iterate through each cell in the range C5:C12. This range is hardcoded, meaning that it will always check those cells regardless of which cell is changed.

If cell.Offset(0, 2).Value <= Target.Value Then

This line checks whether the value in the cell three columns to the right of the current “cell” (i.e. cell E5 if the current cell is C5) is less than or equal to the value of the cell that was changed (as specified by the “Target” parameter). If the condition is true, the code within the “If” block will be executed.

cell.Interior.Color = RGB(144, 238, 144)

This line sets the interior color of the current “cell” to a shade of green specified by the RGB function.

    Else
            cell.Interior.ColorIndex = xlNone

This line is the “Else” block of the previous “If” statement. If the condition in the “If” statement is false, then this line will be executed, which sets the interior color of the current “cell” to none.

End If
    Next cell
    End If
End Sub

Method 2 – Automating Profit Calculation Using Target Address

In this instance when the Selling price is changed the profit will automatically update.

Open the worksheet specified and paste the below code.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sellingPriceColumn As Range
    Dim headerCell As Range
    Dim profitColumn As Range
    Set sellingPriceColumn = Range("E5:E12")
    Set headerCell = Range("F4")
    Set profitColumn = Range("F5:F12")
    If Not Intersect(Range(Target.Address), Range(sellingPriceColumn.Address)) Is Nothing Then
        headerCell.Value = "Profit" ' Update the header cell
        headerCell.Font.Size = 12
        headerCell.Font.Bold = True
        profitColumn.FormulaR1C1 = "=RC[-1]-RC[-2]" ' Calculate the profit for each row
        Range("B4:F12").Borders.LineStyle = xlContinuous
    End If
End Sub

The results are returned as below.

Code Explanation

Private Sub Worksheet_Change(ByVal Target As Range)

This line defines the macro as a worksheet change event, which means it will run every time you make a change to the worksheet.

  Dim sellingPriceColumn As Range
    Dim headerCell As Range
    Dim profitColumn As Range

These lines declare three Range objects used later in the code.

  Set sellingPriceColumn = Range("E5:E12")
    Set headerCell = Range("F4")
    Set profitColumn = Range("F5:F12")

These lines assign the range objects to specific cells on the worksheet.

   If Not Intersect(Range(Target.Address), Range(sellingPriceColumn.Address)) Is Nothing Then

This line checks if the target cell (i.e. the cell that you may have changed) intersects with the sellingPriceColumn range. If it does, it will execute the code inside the If statement.

headerCell.Value = "Profit" ' Update the header cell
        headerCell.Font.Size = 12
        headerCell.Font.Bold = True

These lines update the headerCell with the text “Profit” and format its font size and style.

profitColumn.FormulaR1C1 = "=RC[-1]-RC[-2]" ' Calculate the profit for each row

This line sets the FormulaR1C1 property of the profitColumn range to “=RC[-1]-RC[-2]”, which calculates the profit for each row based on the selling price and cost.

Range("B4:F12").Borders.LineStyle = xlContinuous

This line applies a continuous border style to the range B4:F12, which includes the selling price, cost, and profit columns as well as the header row.

   End If
End Sub

Method 3 – Pop Up MsgBox When a Particular Cell Value of Target Address Changes in Excel

Here any change in production cost will trigger the MsgBox.

Enter the following code in the corresponding private worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CellRepresentative As String
    Dim ProductionCost As Range
    Set ProductionCost = Range("D5:D12") 'assuming production cost is in column D and data starts from row 5 to row 12
    CellRepresentative = Target.Offset(0, -2).Value 'assuming cell representative is in column B
    If Not Intersect(Target, ProductionCost) Is Nothing Then
        If MsgBox("Warning: Production cost of " & CellRepresentative & " (" & Target.Address & ") has been changed!", vbOKOnly + vbExclamation, "Production Cost Change") = vbOK Then
            'do nothing or add code to perform an action
        End If
    End If
End Sub

A change of cell value returns the below message.

Code Explanation

Private Sub Worksheet_Change(ByVal Target As Range)

This line defines the macro as a worksheet change event, which means it will run every time you make a change to the worksheet.

    Dim CellRepresentative As String
    Dim ProductionCost As Range

These lines declare a string variable (CellRepresentative) and a range variable (ProductionCost) used later in the code.

Set ProductionCost = Range("D5:D12")

This line assigns the ProductionCost range object to the range of cells D5:D12, which contains the production cost data.

CellRepresentative = Target.Offset(0, -2).Value

This line assigns the value of the cell that is two columns to the left of the target cell (i.e. the cell that you have changed) to the CellRepresentative variable. This assumes that the cell representative data is in column A.

If Not Intersect(Target, ProductionCost) Is Nothing Then

This line checks if the target cell (i.e. the cell that you have changed) intersects with the ProductionCost range. If it does, it will execute the code inside the If statement.

If MsgBox("Warning: Production cost of " & CellRepresentative & " (" & Target.Address & ") has been changed!", vbOKOnly + vbExclamation, "Production Cost Change") = vbOK Then

This line displays a message box that warns the user they have changed the production cost for the cell representative.

The message includes the CellRepresentative value and the address of the changed cell.

The vbOKOnly + vbExclamation arguments specify that the message box should have an exclamation mark icon and an “OK” button. If the user clicks the “OK” button, it will execute the code inside the If statement.

            'do nothing or add code to perform an action
        End If
    End If
End Sub

Intersect Method For Single Cell in Excel VBA

Go to Developer then Visual Basics and select Microsoft Excel objects.
Select the corresponding worksheet and enter the following code.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sellingPriceColumn As Range
    Dim headerCell As Range
    Dim profitColumn As Range
    Set sellingPriceColumn = Range("E5:E12")
    Set headerCell = Range("F4")
    Set profitColumn = Range("F5:F12")
    If Not Intersect(Target, sellingPriceColumn) Is Nothing Then
        headerCell.Value = "Profit" ' Update the header cell
        headerCell.Font.Size = 12
        headerCell.Font.Bold = True
        profitColumn.FormulaR1C1 = "=RC[-1]-RC[-2]" ' Calculate the profit for each row
        Range("B4:F12").Borders.LineStyle = xlContinuous
    End If
End Sub

The output is returned as below.

Preventing Event Loops in VBA

Now we are going to increase the selling price by one dollar recursively. But if you use the code below with each runtime, the worksheet_change event changes the content of a cell that itself is part of the Target Range (ie. triggering the change event), which will result in reprocessing the change event repeatedly, also known as a recursive loop.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("E5:E12")) Is Nothing Then
Range("H5").Value = Range("H5").Value + 1
End If
End Sub

A recursive loop might result in a ‘Out Of Stack Space’ untrappable error, or depending on the Excel setting, the loop might terminate at a threshold limit of say 100. To prevent this, enter the following additional information at the beginning of the code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next  'skip all run-time errors
If Target.Address = "$D$5" Or Target.Address = "$F$5" Then Exit Sub
Application.EnableEvents = False
If Target.Column = 4 Or Target.Column = 5 Then
If Target Then
Range("H5").Value = Range("H5").Value + 1
End If
End If
Application.EnableEvents = True
On Error GoTo 0  'Turn off error trapping and re-allow run time errors
End Sub

Now we get the output of how many numbers has changed without an Out of Space Error.

Code Explanation

Private Sub Worksheet_Change(ByVal Target As Range)

Whenever a change occurs in the worksheet, it will trigger this macro. The macro takes the Target cell (the cell that you have changed) as a parameter.

On Error Resume Next  'skip all run-time errors

This line turns on error handling, which means if an error occurs during the execution of the macro, VBA will continue executing the code from the next line rather than stopping with an error message.

If Target.Address = "$D$5" Or Target.Address = "$F$5" Then Exit Sub

This line checks if the Target cell is either D5 or F5. If it is, the macro exits without executing any further code.

Application.EnableEvents = False

Turns off event handling, which means that any changes made to the worksheet during the execution of the macro will not trigger other macros or events.

If Target.Column = 4 Or Target.Column = 5 Then

This line checks if the Target cell is in column D or E (columns 4 and 5, respectively).

If Target Then

This line checks if the Target cell is not empty.

Range("H5").Value = Range("H5").Value + 1

Increments the value of cell H5 by 1.

End If
End If

These lines end the If statements that checked if the Target cell is in column D or E and if it is not empty.

Application.EnableEvents = True\

This line turns on event handling again so that changes made to the worksheet after the execution of the macro will trigger other macros or events.

On Error GoTo 0  'Turn off error trapping and re-allow run time errors

This line turns off error handling so that any errors that occur after this point will stop the execution of the macro and display an error message.

End Sub

Frequently Asked Questions (FAQs)

Q: Why is the VBA target address important?

A: The VBA target address is important because it allows the VBA code to identify and manipulate the selected cells, enabling the automation of repetitive tasks and increasing productivity.

Q: How do I access the target address in the VBA code?

A: You can access the target address in the VBA code by using the “Target” parameter, which is passed to the Worksheet_SelectionChange event. You can then use this parameter to determine the address of the selected cell or range of cells.

Q: How can I use the target address in the VBA code?

A: You can use the target address in VBA code to perform a wide range of tasks, such as changing the formatting of the selected cells, manipulating data in the selected cells, or triggering other events based on the user’s selection.

Q: Can I change the target address in the VBA code?

A: Yes, you can change the target address in the VBA code by using the “Range” object and specifying a new cell or range of cells. However, it is important to be careful when modifying the target address, as it can cause unintended consequences in your Excel workbook.

Q: What are some common mistakes to avoid when working with the target address in the VBA code?

A: Some common mistakes to avoid when working with the target address in VBA code include not checking for null values, not properly handling errors, and not fully understanding the scope of your code.

Things to Remember

Target Address is a property of the Range object in VBA, and it represents the address of the cell that triggered a worksheet event.
When working with worksheet events, you can use Target Address to determine which cell triggered the event and take appropriate actions based on that information.
To access the value of the cell that triggered the event, you can use the Value property of the Range object, like this: Range(Target.Address).Value or Target.Value,
It’s important to validate the Target Address to ensure that it refers to a valid cell in the worksheet. You can use the IsEmpty and IsNumeric functions to do this.
If the Target Address refers to a range of cells, you can use the Intersect function to determine if the range intersects with a specific cell or range of cells.
Keep in mind that the Target Address property is only available within worksheet event procedures, such as Worksheet_Change, Worksheet_SelectionChange, Worksheet_Activate, etc.
Finally, be sure to test your code thoroughly to ensure that it works as expected and handles all possible scenarios that may arise.

Download Practice Workbook

Using Target Address.xlsm