How to Interact with a Target Cell Using Excel VBA (9 Examples)

Understanding the Target Cell Property

When you want to interact with a cell in a worksheet using an event, you can utilize the Target property of Excel. These events are triggered when you perform actions like changing the value of a cell or selecting a range. One commonly used event is the Worksheet Change event, which fires whenever a cell is modified in the worksheet. The Target cell refers to the cell that was changed by the user, and we can use it to perform specific tasks such as formatting or displaying a message box based on the user’s input.


Enabling the VBA Environment

Before we dive into the examples, make sure you have the VBA environment set up in Excel. Here are two ways to access it:

  • Using the Module Window
    • Open the Developer tab (if you don’t see it, enable it).
    • Select the Visual Basic command.

Developer Tab Opening for excel vba target cell

    • The Visual Basic window will open, and you can insert a Module from the Insert menu to enter your VBA code.

Inserting Module

  • Utilizing the Sheet Code Window
    • Right-click on the worksheet name and choose View Code.

Opening Code Window in a specific worksheet

    • Enter your code in the window that appears.

Code window image


We’ll use the below dataset containing Sales Data to demonstrate the examples:

Excel VBA Target Cell


Example 1 – Checking If Target Cell Contains a Certain Value

Suppose we have a dataset called Sales Data, and we want to check if the Total Sales values in cells F12 and F13 exceed a target value of 30.

Dataset of checking if Target Cell contains certain value

We can achieve this using the following VBA code:

Checking If Target Cell Contains Certain Value Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value >= Range("H5").Value Then
msgbox "Target cell value is achieved (" & Target.Value & ")"
End If
End Sub

Code Explanation

This code checks whether the value in the modified cell (Target) is greater than or equal to the value in cell H5. If so, it displays a message indicating that the target value has been achieved.

Read More: How to Use Target Value in Excel VBA


Example 2 – Updating Target Cell Value

In this scenario, we’ll enter Product IDs into cells starting from E5. Note that the Product IDs contain the prefix PI. Our dataset looks like this:

Dataset of Updating Target Cell Value

To automatically insert the prefix in the Product ID data using VBA, we’ll create the following macro:

Code for Auto Updating

Private Sub Worksheet_Change(ByVal Target As Range)
Dim initialCellValue As String
If Not Intersect(Target, Range("E5:E13")) Is Nothing Then
Application.EnableEvents = False
initialCellValue = Target.Value
Target.Value = "PI" & initialCellValue
Application.EnableEvents = True
End If
End Sub

Code Breakdown

  • The Worksheet_Change sub-procedure is triggered when a cell value changes.
  • We declare a string variable called initialCellValue to store the user-inserted value.
  • If the change occurs within the specified range (E5:E13), we prevent infinite loops by temporarily disabling event triggers (Application.EnableEvents = False).
  • The macro concatenates the prefix “PI” with the initial cell value and updates the cell.
  • Finally, we re-enable event handling (Application.EnableEvents = True).

When we insert a Product ID value in cells E5:E13, the prefix “PI” is automatically added.


Example 3 – Setting Font Style to Bold for Target Cell

In this case, we want to bold a specific Salesperson ID, which is “SP009.” Our dataset looks like this:

Dataset for Bolding Target cell

To achieve this using VBA, we’ll enter the following code:

Code for Bolding Target Cell

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B5:B13")) Is Nothing Then
If Target.Value = "SP009" Then
Target.Font.Bold = True
End If
End If
End Sub

Code Breakdown

  • The Worksheet_Change sub-procedure checks if the changed cell is within the specified range (B5:B13).
  • If the value in the cell is “SP009,” it sets the font style to bold.

When we enter “SP009” in the specified range, the font becomes bold.


Example 4 – Changing Font Color of Target Cell

In this example, we’ll change the font color from the default to blue when a change occurs in the specified range (B5:B13). Our dataset looks like this:Dataset for Changing Font Color of Target Cell

To set the font color of the target cell using VBA, we’ll enter the following macro:

Code for Changing Font Color of Target Cell

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B5:B13")) Is Nothing Then
Target.Font.ColorIndex = 5
End If
End Sub

Code Breakdown

  • The Worksheet_Change sub-procedure checks if the changed cell is within the specified range (B5:B13).
  • If so, it changes the font color to ColorIndex 5 (blue).

Now, any changes in the specified range will update the font color accordingly.


Example 5 – Uppercasing Target Cell

In this example, we’ll convert the input of cells within our specified range (B5:B13) to uppercase. Our dataset looks like this:

Uppercasing Target Cell Dataset

The data under the “Salesperson ID” column must be in uppercase. To achieve this, we’ll use VBA. Here’s the code snippet:

Code for Upper Casing Target Cell

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B5:B13")) Is Nothing Then
Application.EnableEvents = False
'Convert the values to uppercase
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End If
End Sub

Code Breakdown

  • The Worksheet_Change sub-procedure checks if the change occurred within the specified range (B5:B13) using the Intersect function.
  • To prevent infinite loops, we temporarily disable event triggers with Application.EnableEvents = False.
  • The macro converts the value of the target cell to uppercase using UCase(Target.Value).
  • Finally, we re-enable event handling with Application.EnableEvents = True.

Now, any changes in the specified range will automatically convert the cell values to uppercase.


Example 6 – Enabling a Double Click Event with Target Cells

In this case, we’ll demonstrate how to enable a double-click event with the target cell. First, we’ll specify a range of cells in the worksheet. Then, if we double-click on any specific cell within that range, a message will display the cell value.

Our dataset looks like this:

Enabling a Double Click Event with Target Cells Dataset

To achieve this using VBA, we’ll enter the following code:

Code for Enabling a Double Click Event with Target Cells

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("B5:F13")) Is Nothing Then
msgbox "Selected cell value is " & Target.Value
End If
End Sub

Code Breakdown

  • The Worksheet_BeforeDoubleClick sub-procedure checks if the double-clicked cell is within the specified range (B5:F13) using Intersect.
  • If so, it displays a message with the cell value.

Now, when we double-click on any cell within the specified range (e.g., F7), the value of that cell will be displayed.

Read More: How to Use Excel VBA Target Range


Example 7 – Automatically Updating Columns Based on Changes in the Target Cell

In Excel VBA, we can leverage the Target cell property to automatically update columns when changes occur. For instance, whenever we modify data in a worksheet, we might want to propagate those changes to another worksheet. Let’s walk through an example.

Source Worksheet to make change

Scenario

  1. We have a worksheet named “Sheet19” where we’ll make changes to cell values.
  2. Whenever a change occurs in “Sheet19,” we want to populate the modified cell values in a different worksheet called “Sheet21.”

Data populates to this worksheet

VBA Code

Code for Updating Columns Automatically Based on Changes in Target Cell

Private Sub Worksheet_Change(ByVal Target As Range)
Dim pasteRange As Range
If Not Intersect(Target, Me.Range("F14:F100")) Is Nothing Then
Set pasteRange = Sheet21.Range("B" & Sheet19.Rows.Count).End(xlUp).Offset(1)
Me.Range("B" & Target.Row & ":F" & Target.Row).Copy pasteRange
End If
End Sub

Code Breakdown

  • The Worksheet_Change sub-procedure checks if the changed cells (Target) are within the range F14:F100 of the current worksheet (Me).
  • It sets pasteRange to the first empty cell in column B of “Sheet21.”
  • The code copies the entire row (columns B to F) from the changed cell and pastes it into pasteRange.

Now, any changes in the Populate1 worksheet will automatically update the corresponding columns in the Populate2 worksheet. Watch the following video for an example where we’ve modified values in row 14 of the Populate1 worksheet.

Read More: Excel VBA: How to Use Target Row


Example 8 – Coloring Matched Values

Suppose we have a dataset, and we want to search for specific products. Whenever a product in cell H5 matches an entry in the dataset (column D), we want to highlight that product in column D. Let’s achieve this using VBA.

Dataset

Dataset of Coloring Matched Values

Now, every time we get the products matching our search product in cell H5, we want the Product to be colored in the dataset in column D. We can do this using VBA.

VBA Code

The following image contains the VBA code to do so.

Code for Coloring Matched Values

Private Sub Worksheet_Change(ByVal Target As Range)
Dim product As String
Dim found As Range
Dim firstAddress As String
' Check if the change was made in cell H5
If Target.Address = "$H$5" Then
' Get the product name from cell H5
product = Target.Value
' Clear the interior color of all cells in column D
Range("D5:D13").Interior.ColorIndex = xlColorIndexNone
' Search for the product in column D
Set found = Range("D5:D13").Find(product, LookIn:=xlValues, LookAt:=xlWhole)
' If the product is found, color the cell(s)
If Not found Is Nothing Then
firstAddress = found.Address
Do
found.Interior.Color = RGB(204, 255, 255)
Set found = Range("D5:D13").FindNext(found)
Loop While Not found Is Nothing And found.Address <> firstAddress
Else
' If the product is not found, show a message and clear the cell color
' disable events to prevent infinite loop
Application.EnableEvents = False
msgbox product & " not found in dataset"
Target.ClearContents
' re-enable events
Application.EnableEvents = True
End If
End If
End Sub

Code Breakdown

  • The Worksheet_Change sub-procedure checks if the change occurred in cell H5.
  • It retrieves the product name from H5 and clears the interior color of all cells in column D.
  • The code searches for the product in column D and colors the matching cell(s) with a light blue shade.
  • If the product is not found, it displays a message and clears the cell content.

Now, if we search for any product in the dataset, for example, TV, then we need to enter TV in cell H5.


Example 9 – Coloring Cells Based on Conditions

In Excel VBA, we can use the Target cell property to dynamically color cells based on specific conditions. Let’s explore two scenarios: coloring cells containing integer values and coloring cells containing string values.


9.1 Color Cells Containing Integer Values Based on Condition

Suppose we want to color the Total Sales values based on specific criteria. Initially, our dataset looks like this:

Dataset for coloring integer values

We’ll automatically color the cells red if their values fall between 100 and 200. Let’s achieve this using VBA. Here’s the code snippet:

Code for coloring integer values

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column = 6 Then
        CurrentRow = Target.Row
        If Target.Value > 100 And Target.Value < 200 Then
            Range("F" & CurrentRow).Interior.ColorIndex = 3
        Else
            Range("F" & CurrentRow).Interior.ColorIndex = xlColorIndexNone
        End If
    End If
End Sub

 

Code Breakdown

  • The Worksheet_Change sub-procedure checks if the changed cell is in the 6th column (column F).
  • If the value falls within the specified range (100 to 200), it colors the cell red; otherwise, it removes any existing color.

Now, if we enter the Total Sales values in those cells, the cells containing values between 100 and 200 will be colored red automatically.


9.2 Color Cells Containing String Values Based on Condition

Now let’s consider coloring cells based on specific string values. Suppose we want to highlight products in the Product column with different colors. We’ll use separate colors for TV, Phone, and Fridge. Our dataset looks like this:

Dataset for coloring String values

To achieve this using VBA, we’ll enter the following code:

Code for coloring String values

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim ThisRow As Long
If Target.Column = 4 Then
ThisRow = Target.Row
Select Case Target.Value
Case "TV"
Range("D" & ThisRow).Interior.Color = RGB(255, 0, 255)
Case "Phone"
Range("D" & ThisRow).Interior.Color = RGB(204, 255, 255)
Case "Fridge"
Range("D" & ThisRow).Interior.Color = RGB(51, 204, 204)
Case Else
Range("D" & ThisRow).Interior.ColorIndex = xlColorIndexNone ' no color
End Select
End If
End Sub

Code Breakdown

  • The code checks if the changed cell is in the 4th column (column D).
  • Depending on the product name, it applies different colors (magenta, light blue, or teal) or removes any existing color.

Now if we enter the Product in the cells in column D, the cells will color accordingly.


How to Write to Multiple Cells Using the Target.Address Method in Excel VBA

Sometimes, we need to perform tasks based on specific conditions in Excel. For instance, if we want to calculate the total sales of the first two salespeople, we can achieve this using the Target.Address property in VBA. Let’s explore how to do this.

Scenario

Suppose we have a dataset with Total Sales values, and we want to compute the non-zero sum of the Total Sales for the first two salespeople.

Excel VBA Target.Address Dataset

VBA Code Example

Code of Using Target.Address

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim total As Double
Select Case Target.Address
Case "$F$5", "$F$6"
If Target.Value = 0 Then Exit Sub
On Error GoTo errorHandler
Application.EnableEvents = False
Target.Value = Target.Value + oldvalue
total = WorksheetFunction.Sum(Range("F5,F6"))
Range("G5").Value = total
Case Else
Exit Sub
End Select
errorHandler:
Application.EnableEvents = True
End Sub

Code Breakdown

  • The Worksheet_Change sub-procedure runs whenever a change occurs in the worksheet.
  • We declare a total variable to store the sum of Total Sales values in cells F5 and F6.
  • The Select Case block checks if the changed cell address matches F5 or F6.
  • If the value in the changed cell is zero, the sub-procedure exits.
  • Otherwise, it adds the current value to the previous value and computes the total.
  • The result is displayed in cell G5.

Now, whenever you modify the Total Sales values in cells F5 or F6, the Target.Address method calculates the non-zero sum for the first two salespeople.


Key Takeaways

By following this article, you’ll learn how to:

  • Efficiently use the Target cell property.
  • Work with both numeric and string data using the Target cell.
  • Format cells based on conditions using the Target cell.

Download Practice Workbook

You can download the practice workbook from here:


 

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Hadi Ul Bashar
Hadi Ul Bashar

Hadi Ul Bashar holds a BSc degree in Biomedical Engineering from Bangladesh University of Engineering and Technology.  In January 2023, Bashar joined SOFTEKO as an Excel and VBA content developer. Since then, he has authored over 50 articles. He was engaged in two specialized training programs focused on VBA and Chart & Dashboard designing in Excel. Currently he is creating video content on Excel and VBA and has created over 70 videos. Reading books and traveling are his... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo