Excel VBA Target Cell: 9 Practical Examples

Looking for ways to interact with a target cell using VBA in Excel? Then, this is the right place for you.

If you want to interact with a cell in the worksheet by using an event, you may use the Target property of Excel. These events can be triggered when you interact with the worksheet. Changing the value of a cell or selecting a range are such examples of events. And the Worksheet Change event is one of the most commonly used events. This is triggered whenever a cell is changed in the worksheet. The Target cell is a property of the Worksheet Change event. It refers to the cell that was changed by the user. We can use the Target cell to perform some specific tasks. For example, we can do the formatting, displaying a message box, etc based on the value entered by the user in the cell.

In this article, I’m going to demonstrate practical examples of how we can use Excel VBA Target cell in detail. To get a clear understanding of how it works, you may follow through with this article.

How to Launch VBA Editor in Excel

As we’ll use the VBA environment throughout this work, we need to enable the VBA setup first if not done so.

Using Module Window:

First of all, open the Developer tab. If you don’t have the developer tab, then you have to enable the developer tab. Then select the Visual Basic command.

Hence, the Visual Basic window will open. Then, we’ll insert a Module from the Insert option where we’ll write the VBA code.

Utilizing Sheet Code Window:

You can do the task in another way. If you want to keep your macro worksheet specific, that is you want a macro to be executed in an assigned worksheet only, you can simply follow this:
Right Mouse Click on the Worksheet Name>View Code

Now you can write the code in the following window.

Excel VBA Target Cell: 9 Practical Examples

We can use the Target cell for various purposes. We can format any cell based on the value change of the Target cell, display a message box, and so on. I’m going to show the practical use cases of the Target cell in the following Examples. I’ll demonstrate these examples on “Sales Data”. Our dataset looks like this.

1. Checking If Target Cell Contains Certain Value

In this example, we’ll insert the ‘Total Sales’ in cells F12 and F13. Also, we need to check whether the ‘Total Sales’ values in cells F12 and F13 are larger than a Target Value of 30. Our dataset looks like this.

If the values in the cells F12 and F13 are greater than the Target Value, a message will pop up, otherwise, nothing will happen.

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

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

Now, if we input the Total Sales values in cells F12 and F13, we’ll get output like the following video. Thus, you can check if a certain value is present in the target cell using VBA in Excel.

Read More: How to Use Target Value in Excel VBA

2. Updating Target Cell Value

Here, we’ll input the Product ID in the cells starting from cell E5. Note that the Product ID contains a prefix PI. Our dataset looks like this.

We’ll use VBA to insert the prefix in the Product ID data automatically.

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

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

Now if we insert the Product ID value in the cells E5:E13, then the prefix PI is automatically added before the value we’ll insert. The following video shows the output.

3. Setting Font Style to Bold for Target Cell

In this case, we need to bold a specific Salesperson ID which is SP009. Our dataset looks like this.

We’ll use VBA to get our task done. The following image contains the code to do so.

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

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

Now, if we type ‘SP009’ in the specified range of cells (B5:B13), then the font of ‘SP009’ is set as bold. The following video demonstrates this.

4. Changing Font Color of Target Cell

Here, we’ll change the font color from the default color if we make a change in the specified range of cells, that is cells B5:B13. Our dataset looks like this.

We’ll use VBA to set the font color of the Target Cell in Excel. We’ll use the following macro to do so.

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

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

Now, if we change the value of any cells within the range B5:B13, the font color changes automatically. The following video shows an example of the change in cell B13.

5. Uppercasing Target Cell

This time we’ll convert the input of cells within our specified range of cells to uppercase. Our dataset looks like this.

The data under the Salesperson ID column must be in upper case. So, we need to set the range of cells B5:B13 to be in upper case. We’ll do this using VBA. The following image contains the code to do so.

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

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B5:B13")) Is Nothing Then
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End If

End Sub

If we change the values of the specified range of cells, that is the cells in the range of B5:B13, then this macro automatically converts the value of that cell to uppercase. The following video shows such an example.

6. Enabling a Double Click Event with Target Cells

In this case, we’ll show an example of enabling a double-click event with the Target cell. We’ll specify a range of cells in the worksheet first. Then, if we double-click on any of the specific cells in the range, a message will display the cell value.

Our dataset looks like this.

We’ll perform the task using VBA. The following image shows the code to do this.

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

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

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

Now, if we double-click on any of the cells within the specified range of cells (B5:F13), then the value of the cell will be displayed in a message. For example, if we double-click on the F7 cell, the value of F7 will be displayed. The following video shows it properly.

Read More: How to Use Excel VBA Target Range

7. Update Columns Automatically Based on Changes in Target Cell

We can use the Target cell property in Excel VBA to update columns automatically based on changes in the Target cell. For example, whenever we make any change in a worksheet, we want to populate the changed data to another worksheet. The following image shows the worksheet where we will make changes to the cell values. Note that this is Sheet19 in the current workbook.

Now if we make any changes in the values of this worksheet, we want to populate the changed cell values to a different worksheet. The following image shows the worksheet where the change populates. Note that this is Sheet21 in the current workbook.

Now, we’ll use VBA to perform the task. The following image contains the code to do so.

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

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

Now, if we make any change in the Populate1 worksheet, it automatically populates to the Populate2 worksheet, that is the columns of the worksheet get updated automatically. The following video shows an example of this where we’ve changed the values in row 14 of the Populate1 worksheet.

Read More: Excel VBA: How to Use Target Row

8. Coloring Matched Values

We can use the Target cell property to color the matched value in a dataset. For example, we have the following dataset and we want to search for products from this dataset.

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.

The following image contains the VBA code to do so.

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

Private Sub Worksheet_Change(ByVal Target As Range)

Dim product As String
Dim found As Range
Dim firstAddress As String

If Target.Address = "$H$5" Then
product = Target.Value
Range("D5:D13").Interior.ColorIndex = xlColorIndexNone
Set found = Range("D5:D13").Find(product, LookIn:=xlValues, LookAt:=xlWhole)

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
Application.EnableEvents = False
msgbox product & " not found in dataset"
Target.ClearContents
Application.EnableEvents = True
End If

End If

End Sub

Now, if we search for any product in the dataset, for example, TV, then we need to type TV in cell H5. The following video shows the sample output.

9. Color Cells Based on Condition

We can color Target cell based on condition using VBA in Excel. This condition can be both numeric and string. I’ll demonstrate both in the following examples.

9.1 Color Cells Containing Integer Values Based on Condition

This time, we’ll color the Total Sales values based on condition. We’ll use Target cell property to achieve this. For example, we’ll color the values between 100 and 200 Red. Initially, our dataset looks like this.

Now, we’ll enter the Total Sales data in cells F12 and F13. And color the cells red automatically if the cell values are between 100 and 200.

We’ll do so using VBA. The following image contains the VBA code.

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

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

Now, if we type the Total Sales values in those cells, the cells containing values between 100 and 200 will be colored red automatically. The following video shows such an example.

9.2 Color Cells Containing String Values Based on Condition

We can color cells that contain specific string values. For example, we may need to color the cells in the Product column based on the name of the product. We’ll need separate colors for the products TV, Fridge, and Phone. Note that we won’t color the cells containing the Computer. Our dataset looks like the following image.

Now we’ll use VBA to color the cells under the ‘Product’ column accordingly. The VBA code to do so is as follows.

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

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
            End Select
End If

End Sub

Now if we type the Product in the cells in column D, the cells will color accordingly. The following video shows it clearly.

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

Sometimes we need to perform tasks based on conditions. For example, if we want to get the summation of the the Total Sales of the first two Salesperson, we can do this using Target.Address property in Excel VBA. Our dataset looks like the following image.

Now, we’ll use VBA to get the non-zero summation of the Total Sales of the first two Salesperson(s).

The VBA code to do so is in the following image.

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

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

Now, whenever we change the ‘Total Sales’ values in the F5 or F6 cell, the Target.Address computes the non-zero sum of the ‘Total Sales’ of the first two salesperson. Check the following video to see how it works.

Takeaways from This Article

If you’ve followed through the article, you should be now able to:

• Use Target cell property efficiently.
• Utilize Target cell in both numeric and string data.
• Format cells based on conditions using Target cell.

Download Practice Workbook

You can download our practice workbook from here for free!

Conclusion

In this article, I’ve demonstrated the use of Excel VBA Target cell. I’ve demonstrated real-life examples of using Target cell. Again, I’ve explained the VBA macros in detail so that you can understand them properly. If you practice a little bit, I hope you’ll be able to use the Target cell property in our problems and get useful solutions. If you face any issues regarding this, please let me know in the comment section. I’ll be happy to solve your problem. Have a nice day!