How to Use Target Value in Excel VBA

Get FREE Advanced Excel Exercises with Solutions!

One of the most powerful features in Excel VBA is the ability to use Target.Value method, which allows users to monitor changes made to their spreadsheet and take action accordingly. In this article, I’ll show you how to use the target value in Excel VBA.

In VBA, the “Target” value refers to the cell or range that was changed by the user or by a macro. By using the “Target” value, users can create macros that respond to specific changes in their spreadsheets, making it easier to manage large amounts of data.

This article will explore various uses of the “Target” value in VBA. Whether you are a beginner or an experienced Excel user, learning to use the “Target” value can help you take your Excel skills to the next level and streamline your workflow.


How to Launch Visual Basic Editor in Excel

To Launch Visual Basic Editor, we need to click on Visual Basic under the Developer tab.

Opening Visual Basic Window

Then, double-click on the sheet name inside which you want to write the code. Finally, inside the worksheet event, you can write suitable codes to get your desired output. You have to repeat this process for adding each of the new Macros. (If you are using VBA for the first time, you may need to add the Developer tab inside the ribbon in Excel)

Writing Space for the Generated Codes


Overview of Target.Value Property in Excel VBA

In Excel VBA, a “Target Value” refers to the value of a specific cell or range of cells in a worksheet that triggers a certain action or set of actions. When the target value is changed, Excel VBA can be programmed to respond with various behaviors, such as recalculating formulas, updating other cells, or displaying messages.

The Worksheet_Change event is commonly used to detect changes in target values. This event is triggered when a user makes a change to a cell or range of cells in the worksheet. By writing VBA code within the Worksheet_Change event procedure, you can instruct Excel to perform certain tasks based on the value of the target cell or range.

You can check the value of a target cell or range using the Target object. The Target object is automatically populated with the cell or range that was changed when the Worksheet_Change event is triggered.

Here is the syntax to check the value of a single target cell:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
'Check if the value of the target cell is "Hello"
If Target.Value = "Hello" Then
'do something
End If
End If
End Sub

In this example, the If statement checks if the target cell’s address is equal to “$A$1”. If the target cell matches the specified address, the code inside the If statement is executed.

An additional condition is added to check if the value of the target cell is “Hello”. If the value is true “Hello”, then the code inside the “if” statement will be executed. You can modify this condition to check for any specific value that you need.

You can also check the value of a range of cells by using the Intersect() method. The Intersect() method returns a range that represents the overlapping area between two or more ranges. Here is an example that checks if any cell in the range “A1:C5” was changed:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim checkRange As Range
Set checkRange = Range("A1:C5")
If Not Intersect(Target, checkRange) Is Nothing Then
'do something
End If
End Sub

In this example, the Intersect() method checks if the Target range intersects with the checkRange range. If there is any overlap between the two ranges, the code inside the If statement is executed.


Target Value in VBA: 9 Suitable Examples

We have taken a dataset named “Mid-Term Results of Ten Students” to demonstrate these 9 examples to you.

Sample Dataset Containing Marks of Students


1. Use of VBA Target.Address Property

In this example of the usage of Target.Address property, the code monitors changes made to a specific cell in an Excel worksheet (“$E$7”), and when the cell is changed, it displays a message box that informs the user of the new value of the cell.

Steps:

  • First, double-click on the worksheet named “Ex1” as shown before.
  • Then, write the following code inside the Worksheet.

Code Image of VBA Target.Address Property

Code Syntax:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$7" Then
Dim updatedValue As Variant
updatedValue = Target.Value
MsgBox "The value of cell E7 has been changed to " & updatedValue
End If
End Sub

Code Breakdown:

  • “Private Sub Worksheet_Change(ByVal Target As Range)”: This line starts the subroutine that will run whenever there is a change made to the worksheet. The “Private” keyword means that this subroutine can only be accessed within the worksheet where it is contained. “Worksheet_Change” is the event that will trigger the code to run, and “Target” is a variable that represents the cell or range of cells that was changed.
  • “If Target.Address = “$E$7″ Then”: This line checks whether the cell that was changed is the one with the address “$E$7”. If the condition is true, the code inside the “If” statement will run. If the condition is false, the code inside the “If” statement will be skipped.
  • “Dim updatedValue As Variant”: This line declares a variable called “updatedValue” as a variant data type. The “Variant” data type is a data type that can hold any type of data.
  • “updatedValue = Target.Value”: This line assigns the value of the changed cell to the “updatedValue” variable. The “Value” property of a cell represents the value that is currently stored in the cell.
  • “MsgBox “The value of cell E7 has been changed to ” & updatedValue”: This line displays a message box that informs the user that the value of cell E7 has been changed, and it also displays the new value of the cell. The “&” symbol is used to concatenate (combine) the text string “The value of cell E7 has been changed to ” with the value of the “updatedValue” variable.
  • “End If”: This line ends the “If” statement that began on line 2.
  • “End Sub”: This line ends the subroutine.
  • Consequently, you need to change the value of cell E7.
  • Finally, you will observe the following output.

Detecting Change in Cell E5 and Showing Changed Value in a MsgBox


2. Enabling Events Based on Conditions

In this example, the code monitors changes made to a specific cell in an Excel worksheet (“$C$5”), and when the cell is changed, it accumulates the value of the changed cell in another cell (“D5”). It uses the “IsNumeric” function to check if the values in the cells are numeric before performing the accumulation. The code disables events to prevent the “Worksheet_Change” event from firing recursively and then re-enables events when it is done.

Steps:

  • First, we need to double-click on the worksheet named “Ex2” as shown before.
  • Then, we need to write the following code inside the Worksheet.

use of target value vba Code for Enabling Events Based on Conditions

Code Syntax:

Private Sub Worksheet_Change(ByVal Target As Range)
'Check if the target cell is C5
If Target.Address = "$C$5" Then
'Disable events to prevent Worksheet_Change from firing recursively
Application.EnableEvents = False
'Do some processing here...
'For example, accumulate the value of the changed cell in cell D5
Dim oldValue As Double
Dim newValue As Double
If IsNumeric(Range("D5").Value) Then
oldValue = Range("D5").Value
End If
If IsNumeric(Target.Value) Then
newValue = oldValue + Target.Value
End If
Range("D5").Value = newValue
'Re-enable events
Application.EnableEvents = True
End If
End Sub

Code Breakdown:

  • “Private Sub Worksheet_Change(ByVal Target As Range)”: This line starts the subroutine that will run whenever there is a change made to the worksheet. The “Private” keyword means that this subroutine can only be accessed within the worksheet where it is contained. “Worksheet_Change” is the event that will trigger the code to run, and “Target” is a variable that represents the cell or range of cells that was changed.
  • “‘Check if the target cell is C5”: This is a comment line that explains what the code does.
  • “If Target.Address = “$C$5″ Then”: This line checks whether the cell that was changed is the one with the address “$C$5”. If the condition is true, the code inside the “If” statement will run. If the condition is false, the code inside the “If” statement will be skipped.
  • “Application.EnableEvents = False”: This line disables events to prevent the “Worksheet_Change” event from firing recursively.
  • “Dim oldValue As Double” and “Dim newValue As Double”: These lines declare two variables called “oldValue” and “newValue” as Double data types. The Double data type is a data type that can hold decimal numbers.
  • “If IsNumeric(Range(“D5″).Value) Then”: This line checks whether the value in cell D5 is numeric. If it is, the value is assigned to the “oldValue” variable.
  • “If IsNumeric(Target.Value) Then”: This line checks whether the value in the changed cell is numeric. If it is, the “newValue” variable is assigned the sum of the “oldValue” and the value of the changed cell.
  • “Range(“D5″).Value = newValue”: This line sets the value of cell D5 to the value stored in the “newValue” variable.
  • “Application.EnableEvents = True”: This line re-enables events.
  • “End If”: This line ends the “If” statement that began on line 3.
  • “End Sub”: This line ends the subroutine.
  • Consequently, you need to change the value of the cell C5.
  • Finally, you will observe that the changed value of C5 adds up to the existing value of D5. And the final value replaces the current value of cell D5.

Adding New C5 Value to Existing D5 Value and Showing the Result in D5.


3. Detecting Double-Click and Showing MsgBox

In this example, the code runs whenever a user double-clicks on a cell in the worksheet. If the cell that was double-clicked is in column B, it displays a message box with the cell’s address and value and cancels the default double-click behavior.

Steps:

  • First, we need to double-click on the worksheet named “Ex3” as shown before.
  • Then, we need to write the following code inside the Worksheet.

Code Image of Detecting Double Click and Showing MsgBox

Code Syntax:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Check if the target cell is in column B
If Target.Column = 2 Then
'Do some processing here...
'For example, display a message box with the target cell's value
MsgBox "You double-clicked on cell " & Target.Address & " and its value is " & Target.Value'Cancel the default double-click behavior
Cancel = True
End If
End Sub

Code Breakdown:

  • “Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)”: This line starts the subroutine that will run whenever a user double-clicks on a cell in the worksheet. The “Private” keyword means that this subroutine can only be accessed within the worksheet where it is contained. “Worksheet_BeforeDoubleClick” is the event that will trigger the code to run, and “Target” is a variable that represents the cell or range of cells that was double-clicked on. “Cancel” is a Boolean variable that controls whether the default double-click behavior is canceled.
  • “‘Check if the target cell is in column B”: This is a comment line that explains what the code does.
  • “If Target.Column = 2 Then”: This line checks whether the column of the cell that was double-clicked on is column B (column number 2). If the condition is true, the code inside the “If” statement will run. If the condition is false, the code inside the “If” statement will be skipped.
  • “MsgBox “You double-clicked on cell ” & Target.Address & ” and its value is ” & Target.Value“: This line displays a message box with information about the cell that was double-clicked on, including its address and value.
  • “Cancel = True”: This line cancels the default double-click behavior.
  • “End If”: This line ends the “If” statement that began on line 3.
  • “End Sub”: This line ends the subroutine.
  • Consequently, you need to double-click on any of the cells inside Column B.
  • Finally, you will observe the following output.

Message Box Shows After Double Clicking B5 Cell


4. Use of VBA Target.Offset Property

In this example, the code runs whenever a user makes a change to the worksheet. If the changed cell or range of cells intersects with the range of data (excluding headers), it calculates the average score and updates the value in column H with the average score. It also checks whether the average score is greater than or equal to 85 and updates the value in column I with the text “Pass” or “Fail” accordingly.

Steps:

  • First, we need to double-click on the worksheet named “Ex4” as shown before.
  • Then, we need to write the following code inside the Worksheet.

Code Image of Use of VBA Target.Offset Property

Code Syntax:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dataRange As Range
Set dataRange = Range("C5:G14") ' Range containing the data (excluding headers)If Not Intersect(Target, dataRange) Is Nothing Then
' Calculate the average score and update the value in column H
Dim rowRange As Range
Set rowRange = Target.EntireRow ' Range containing the grades for the current student
Dim avgScore As Double
avgScore = Application.WorksheetFunction.Average(rowRange)
Target.Offset(0, 1).Value = avgScore' Check if the student passed or failed based on the average score
If avgScore >= 85 Then
Target.Offset(0, 2).Value = "Pass"
Else
Target.Offset(0, 2).Value = "Fail"
End If
End If
End Sub

Code Breakdown:

  • “Private Sub Worksheet_Change(ByVal Target As Range)”: This line starts the subroutine that will run whenever a user makes a change to the worksheet. The “Private” keyword means that this subroutine can only be accessed within the worksheet where it is contained. “Worksheet_Change” is the event that will trigger the code to run, and “Target” is a variable that represents the cell or range of cells that was changed.
  • “Dim dataRange As Range”: This line declares a variable called “dataRange” that will be used to store the range of cells containing the data (excluding headers).
  • “Set dataRange = Range(“C5:G14″)”: This line sets the value of the “dataRange” variable to a range containing the data (excluding headers). In this case, the range is cells C5 to G14.
  • “If Not Intersect(Target, dataRange) Is Nothing Then”: This line checks whether the changed cell or range of cells intersects with the “dataRange” variable. If the condition is true, the code inside the “If” statement will run. If the condition is false, the code inside the “If” statement will be skipped.
  • “Dim rowRange As Range”: This line declares a variable called “rowRange” that will be used to store the range of cells containing the grades for the current student.
  • “Set rowRange = Target.EntireRow”: This line sets the value of the “rowRange” variable to the entire row containing the changed cell or range of cells.
  • “Dim avgScore As Double”: This line declares a variable called “avgScore” that will be used to store the average score for the current student.
  • “avgScore = Application.WorksheetFunction.Average(rowRange)”: This line calculates the average score for the current student and stores it in the “avgScore” variable.
  • “Target.Offset(0, 1).Value = avgScore”: This line updates the value in the cell one column to the right of the changed cell (i.e., in column H) with the average score for the current student.
  • “If avgScore >= 85 Then”: This line checks whether the average score for the current student is greater than or equal to 85.
  • “Target.Offset(0, 2).Value = “Pass””: This line updates the value in the cell two columns to the right of the changed cell (i.e., in column I) with the text “Pass” if the average score is greater than or equal to 85.
  • “Target.Offset(0, 2).Value = “Fail””: This line updates the value in the cell two columns to the right of the changed cell (i.e., in column I) with the text “Fail” if the average score is less than 85.
  • “End If”: This line ends the “If” statement that began on line 10.
  • “End If”: This line ends the “If” statement that began on line 4.
  • “End Sub”: This line ends the subroutine.
  • Consequently, you need to add or change the values of Column G .
  • Finally, you will observe the following output.

Calculating Average Mark and Giving Remarks


5. Highlighting Cells Based on Cell Value

In this example, the code checks for changes in a specific range of cells in a worksheet, and if a cell’s value in that range changes, it checks if any of the scores is below a threshold value. If any score is below the threshold, the corresponding row is highlighted in red, and the cell to the right of the changed cell is updated to “Fail”. If all scores are above the threshold, the row highlighting is removed, and the cell to the right of the changed cell is updated to “Pass”.

Steps:

  • First, we need to double-click on the worksheet named “Ex5” as shown before.
  • Then, we need to write the following code inside the Worksheet.

Code Image of Highlighting Cells Based on Cell Value

Code Syntax:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim studentScores As Range
Set studentScores = Range("C5:G14") 'the range of student scores in the worksheetIf Not Intersect(Target, studentScores) Is Nothing Then
Dim currentRow As Integer
currentRow = Target.Row 'the row of the changed cellDim threshold As Integer
threshold = 80 'the score threshold below which students will be highlightedDim mathScore As Integer
mathScore = Range("C" & currentRow).Value
Dim scienceScore As Integer
scienceScore = Range("D" & currentRow).Value
Dim englishScore As Integer
englishScore = Range("E" & currentRow).Value
Dim geographyScore As Integer
geographyScore = Range("F" & currentRow).Value
Dim economicsScore As Integer
economicsScore = Range("G" & currentRow).ValueIf mathScore < threshold Or scienceScore < threshold Or englishScore < threshold Or geographyScore < threshold Or economicsScore < threshold Then
Range("B" & currentRow & ":G" & currentRow).Interior.Color = RGB(255, 0, 0) 'highlight the row in red
Target.Offset(0, 1).Value = "Fail"
Else
Range("B" & currentRow & ":G" & currentRow).Interior.ColorIndex = xlNone 'remove any existing highlighting
Target.Offset(0, 1).Value = "Pass"
End If
End If
End Sub

Code Breakdown:

  • “Private Sub Worksheet_Change(ByVal Target As Range)”: This line defines the worksheet event that triggers the macro when a cell value in the worksheet is changed.
  • “Dim studentScores As Range”: This line declares a Range variable called “studentScores”.
  • “Set studentScores = Range(“C5:G14″)”: This line sets the value of the “studentScores” variable to the range “C5:G14” in the worksheet.
  • “If Not Intersect(Target, studentScores) Is Nothing Then”: This line checks if the changed cell is within the “studentScores” range.
  • “Dim currentRow As Integer”: This line declares an Integer variable called “currentRow”.
  • “currentRow = Target.Row”: This line sets the value of the “currentRow” variable to the row number of the changed cell.
  • “Dim threshold As Integer”: This line declares an Integer variable called “threshold”.
  • “threshold = 80”: This line sets the value of the “threshold” variable to 80.
  • “Dim mathScore As Integer”: This line declares an Integer variable called “mathScore”.
  • “mathScore = Range(“C” & currentRow).Value”: This line sets the value of the “mathScore” variable to the value of the cell in column C and the current row.
  • “Dim scienceScore As Integer”: This line declares an Integer variable called “scienceScore”.
  • “scienceScore = Range(“D” & currentRow).Value”: This line sets the value of the “scienceScore” variable to the value of the cell in column D and the current row.
  • “Dim englishScore As Integer”: This line declares an Integer variable called “englishScore”.
  • “englishScore = Range(“E” & currentRow).Value”: This line sets the value of the “englishScore” variable to the value of the cell in column E and the current row.
  • “Dim geographyScore As Integer”: This line declares an Integer variable called “geographyScore”.
  • “geographyScore = Range(“F” & currentRow).Value”: This line sets the value of the “geographyScore” variable to the value of the cell in column F and the current row.
  • “Dim economicsScore As Integer”: This line declares an Integer variable called “economicsScore”.
  • “economicsScore = Range(“G” & currentRow).Value”: This line sets the value of the “economicsScore” variable to the value of the cell in column G and the current row.
  • “If mathScore < threshold Or scienceScore < threshold Or englishScore < threshold Or geographyScore < threshold Or economicsScore < threshold Then”: This line checks if any of the scores is below the threshold value.
  • “Range(“B” & currentRow & “:G” & currentRow).Interior.Color = RGB(255, 0, 0)”: This line sets the interior color of the row containing the changed cell to red.
  • “Target.Offset(0, 1).Value = “Fail” “: This line sets the value of the cell one column to the right of the changed cell to “Fail”.
  • “Else”: This line is the alternative condition when the scores are above the threshold.
  • “Range(“B” & currentRow & “:G” & currentRow).Interior.ColorIndex = xlNone”: This line removes any existing interior color highlighting from the row containing the changed cell.
  • “Target.Offset(0, 1).Value = “Pass” “: This line sets the value of the cell one column to the right of the changed cell to “Pass”.
  • “End If”: This line ends the conditional statement.
  • “End Sub”: This line ends the macro subroutine.
  • Consequently, you need to add or change the values of Column G.
  • Finally, you will observe the following output.

Coloring Entire Rows for Marks Under 80 in Any Subject


6. Displaying MsgBox Based on Certain Conditions

In this example, the code is a VBA macro that is triggered whenever a cell value is changed in a specified range on a worksheet. If a student achieves a perfect score in all subjects, a message box is displayed congratulating them, and the cell next to their name is filled with the text “Perfect Score”.

Steps:

  • First, we need to double-click on the worksheet named “Ex6” as shown before.
  • Then, we need to write the following code inside the Worksheet.

Code Image of Displaying MsgBox Based on Certain Conditions

Code Syntax:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim studentScores As Range
Set studentScores = Range("C5:G14") 'the range of student scores in the worksheetIf Not Intersect(Target, studentScores) Is Nothing Then
Dim currentRow As Integer
currentRow = Target.Row 'the row of the changed cellDim perfectScore As Integer
perfectScore = 100 'the perfect score thresholdDim mathScore As Integer
mathScore = Range("C" & currentRow).Value
Dim scienceScore As Integer
scienceScore = Range("D" & currentRow).Value
Dim englishScore As Integer
englishScore = Range("E" & currentRow).Value
Dim geographyScore As Integer
geographyScore = Range("F" & currentRow).Value
Dim economicsScore As Integer
economicsScore = Range("G" & currentRow).Value
If mathScore = perfectScore And scienceScore = perfectScore And englishScore = perfectScore And geographyScore = perfectScore And economicsScore = perfectScore Then
MsgBox "Congratulations, " & Range("A" & currentRow).Value & "! You achieved a perfect score in all subjects!" 'display a message box
Target.Offset(0, 1).Value = "Perfect Score"
End If
End If
End Sub

Code Breakdown:

  • “Dim studentScores As Range”: This line declares a variable named “studentScores” as a Range data type.
  • “Set studentScores = Range(“C5:G14″)”: This line sets the “studentScores” variable to the range of cells C5 to G14.
  • “If Not Intersect(Target, studentScores) Is Nothing Then”: This line checks if the “Target” range intersects with the “studentScores” range, and executes the code inside the If statement if the condition is true.
  • “Dim currentRow As Integer”: This line declares a variable named “currentRow” as an Integer data type.
  • “currentRow = Target.Row”: This line sets the value of “currentRow” to the row number of the cell in the “Target” range.
  • “Dim perfectScore As Integer”: This line declares a variable named “perfectScore” as an Integer data type.
  • “perfectScore = 100”: This line sets the value of “perfectScore” to 100.
  • “Dim mathScore As Integer”: This line declares a variable named “mathScore” as an Integer data type.
  • “mathScore = Range(“C” & currentRow).Value”: This line sets the value of “mathScore” to the value of the cell in column C of the row number stored in “currentRow”.
  • “Dim scienceScore As Integer”: This line declares a variable named “scienceScore” as an Integer data type.
  • “scienceScore = Range(“D” & currentRow).Value”: This line sets the value of “scienceScore” to the value of the cell in column D of the row number stored in “currentRow”.
  • “Dim englishScore As Integer”: This line declares a variable named “englishScore” as an Integer data type.
  • “englishScore = Range(“E” & currentRow).Value”: This line sets the value of “englishScore” to the value of the cell in column E of the row number stored in “currentRow”.
  • “Dim geographyScore As Integer”: This line declares a variable named “geographyScore” as an Integer data type.
  • “geographyScore = Range(“F” & currentRow).Value”: This line sets the value of “geographyScore” to the value of the cell in column F of the row number stored in “currentRow”.
  • “Dim economicsScore As Integer”: This line declares a variable named “economicsScore” as an Integer data type.
  • “economicsScore = Range(“G” & currentRow).Value”: This line sets the value of “economicsScore” to the value of the cell in column G of the row number stored in “currentRow”.
  • “If mathScore = perfectScore And scienceScore = perfectScore And englishScore = perfectScore And geographyScore = perfectScore And economicsScore = perfectScore Then”: This line checks if all the subject scores in the current row are equal to the perfect score, and executes the code inside the If statement if the condition is true.
  • “MsgBox “Congratulations, ” & Range(“A” & currentRow).Value & “! You achieved a perfect score in all subjects!””: This line displays a message box with a congratulatory message and the name of the student whose scores achieved a perfect score in all subjects.
  • “Target.Offset(0, 1).Value = “Perfect Score” “: This line sets the value of the cell one column to the right of the changed cell to “Perfect Score”.
  • “End If”: This line ends the conditional statement.
  • “End Sub”: This line ends the macro subroutine.
  • Consequently, you need to change all the values of any of the rows from the table to “100”.
  • Finally, you will observe the final Output.

Remarks “Perfect Score” if any Student Achieves 100 in all Subjects.

Read More: Excel VBA: How to Use Target Row


7. Doing Mathematical Operation and Display Result

In this example, this VBA code triggers when a cell value in a specific range (C5:G14) is changed in an Excel worksheet. It calculates the total score of the student in all subjects based on the values of cells C to G in the same row as the changed cell and displays the total score in column H of that row.

Steps:

  • First, we need to double-click on the worksheet named “Ex7” as shown before.
  • Then, we need to write the following code inside the Worksheet.

Code Image of Doing Mathematical Operation and Display Result

Code Syntax:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim studentScores As Range
Set studentScores = Range("C5:G14") 'the range of student scores in the worksheetIf Not Intersect(Target, studentScores) Is Nothing Then
Dim currentRow As Integer
currentRow = Target.Row 'the row of the changed cellDim mathScore As Integer
mathScore = Range("C" & currentRow).Value
Dim scienceScore As Integer
scienceScore = Range("D" & currentRow).Value
Dim englishScore As Integer
englishScore = Range("E" & currentRow).Value
Dim geographyScore As Integer
geographyScore = Range("F" & currentRow).Value
Dim economicsScore As Integer
economicsScore = Range("G" & currentRow).ValueDim totalScore As Integer
totalScore = mathScore + scienceScore + englishScore + geographyScore + economicsScore 'calculate the total scoreRange("H" & currentRow).Value = totalScore 'display the total score in the H column
End If
End Sub

Code Breakdown:

  • “Private Sub Worksheet_Change(ByVal Target As Range)”: This line defines the worksheet event that triggers the macro when a cell value in the worksheet is changed.
  • “Dim studentScores As Range”: This line declares a variable named “studentScores” as a range.
  • “Set studentScores = Range(“C5:G14″)”: This line sets the “studentScores” range to cover the cells from C5 to G14.
  • “If Not Intersect(Target, studentScores) Is Nothing Then”: This line checks if the changed cell is within the “studentScores” range.
  • “Dim currentRow As Integer”: This line declares a variable named “currentRow” as an integer.
  • “currentRow = Target.Row”: This line sets the value of “currentRow” to the row number of the changed cell.
  • “Dim mathScore As Integer”: This line declares a variable named “mathScore” as an integer.
  • “mathScore = Range(“C” & currentRow).Value”: This line sets the value of “mathScore” to the value of the cell in column C of the current row.
  • “Dim scienceScore As Integer”: This line declares a variable named “scienceScore” as an integer.
  • “scienceScore = Range(“D” & currentRow).Value”: This line sets the value of “scienceScore” to the value of the cell in column D of the current row.
  • “Dim englishScore As Integer”: This line declares a variable named “englishScore” as an integer.
  • “englishScore = Range(“E” & currentRow).Value”: This line sets the value of “englishScore” to the value of the cell in column E of the current row.
  • “Dim geographyScore As Integer”: This line declares a variable named “geographyScore” as an integer.
  • “geographyScore = Range(“F” & currentRow).Value”: This line sets the value of “geographyScore” to the value of the cell in column F of the current row.
  • “Dim economicsScore As Integer”: This line declares a variable named “economicsScore” as an integer.
  • “economicsScore = Range(“G” & currentRow).Value”: This line sets the value of “economicsScore” to the value of the cell in column G of the current row.
  • “Dim totalScore As Integer”: This line declares a variable named “totalScore” as an integer.
  • “totalScore = mathScore + scienceScore + englishScore + geographyScore + economicsScore”: This line calculates the total score by adding the values of all subject scores.
  • “Range(“H” & currentRow).Value = totalScore”: This line sets the value of the cell in column H of the current row to the total score.
  • “End If”: This line ends the conditional statement.
  • “End Sub”: This line ends the macro subroutine.
  • Consequently, change any cell value(s) inside the table and the total value would be changed accordingly.
  • Finally, you will observe the output with changed total value(s).

8. Highlighting Maximum Value

In this example, the code highlights the cell with the highest score in a row when a cell value in that row is changed.

Steps:

  • First, we need to double-click on the worksheet named “Ex8” as shown before.
  • Then, we need to write the following code inside the Worksheet.

Code Image of Highlighting Maximum Value

Code Syntax:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim studentScores As Range
Set studentScores = Range("C5:G14") 'the range of student scores in the worksheetIf Not Intersect(Target, studentScores) Is Nothing Then
Dim currentRow As Integer
currentRow = Target.Row 'the row of the changed cellDim highestScore As Integer
'find the highest score in the row
highestScore = WorksheetFunction.Max(Range("C" & currentRow & ":G" & currentRow))
'remove any existing highlighting
Range("B" & currentRow & ":G" & currentRow).Interior.ColorIndex = xlNone
'highlight the cell with the highest score in the row
Range("B" & currentRow & ":G" & currentRow).Find(highestScore).Interior.ColorIndex = 6End If
End Sub

Code Breakdown:

  • “Private Sub Worksheet_Change(ByVal Target As Range)”: This line defines the worksheet event that triggers the macro when a cell value in the worksheet is changed.
  • “Dim studentScores As Range”: This line declares a Range variable named studentScores.
  • “Set studentScores = Range(“C5:G14″)”: This line assigns the range of student scores in the worksheet to the studentScores variable.
  • “If Not Intersect(Target, studentScores) Is Nothing Then”: This line checks if the changed cell is within the studentScores range.
  • “Dim currentRow As Integer”: This line declares an Integer variable named currentRow.
  • “currentRow = Target.Row”: This line assigns the row number of the changed cell to the currentRow variable.
  • “Dim highestScore As Integer”: This line declares an Integer variable named highestScore.
  • “highestScore = WorksheetFunction.Max(Range(“C” & currentRow & “:G” & currentRow))”: This line finds the highest score in the row using the Max function and assigns it to the highestScore variable.
  • “Range(“B” & currentRow & “:G” & currentRow).Interior.ColorIndex = xlNone”: This line removes any existing highlighting from the cells in columns B to G of the current row.
  • “Range(“B” & currentRow & “:G” &   currentRow).Find(highestScore).Interior.ColorIndex = 6″: This line finds and highlights the cell with the highest score in columns B to G of the current row with a color index of 6 (which is yellow).
  • “End If”: This line ends the conditional statement.
  • “End Sub”: This line ends the macro subroutine.
  • Consequently, change any of the values from the table and the highest value in the corresponding row will be highlighted.
  • Finally, you will observe the following output.

Highlighting Maximum Values Inside each Row


9. Use of Worksheet_SelectionChange Event

In this example, the code highlights the entire row of a selected cell if the selected cell is within the specified range of cells.

Steps:

  • First, we need to double-click on the worksheet named “Ex1” as shown before.
  • Then, we need to write the following code inside the Worksheet.

Code Image of Use of Worksheet_SelectionChange Event

Code Syntax:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Check if the selected cell is within the dataset range
If Not Intersect(Target, Range("B5:G14")) Is Nothing Then
' Highlight the selected row
Range(Cells(Target.Row, 2), Cells(Target.Row, 7)).Interior.ColorIndex = 6
End If
End Sub

Code Breakdown:

  • “Private Sub Worksheet_SelectionChange(ByVal Target As Range)”: This line defines the worksheet event that triggers the macro when a cell selection is changed.
  • “‘ Check if the selected cell is within the dataset range”: This line is a comment that explains the purpose of the code that follows.
  • “If Not Intersect(Target, Range(“B5:G14″)) Is Nothing Then”: This line checks if the selected cell is within the specified range of cells (B5:G14) using the Intersect method.
  • “‘ Highlight the selected row”: This line is a comment that explains the purpose of the code that follows.
  • “Range(Cells(Target.Row, 2), Cells(Target.Row, 7)).Interior.ColorIndex = 6”: This line highlights the entire row of the selected cell by setting the background color of cells in columns B to G to yellow (color index 6).
  • “End If”: This line ends the conditional statement.
  • “End Sub”: This line ends the macro subroutine.
  • Consequently, select any of the cells inside the table and the row containing the selected cell will be highlighted instantly.
  • Finally, you will observe the following output.

Coloring Entire Row inside Table by Detecting Selection Cell


Perform Necessary Tasks Before Saving Excel Workbook Using Workbook_BeforeSave Event

The Worksheet_BeforeSave event is a VBA event that is triggered when a user tries to save changes made to a worksheet. This event allows the programmer to perform custom actions or validations before the worksheet is saved.

Below is an example of this Event:

In this example, the VBA code is an event handler that is triggered before the workbook is saved. It checks if all student scores have been entered in the specified range of cells (C5:G14) on Sheet5. If all scores have been entered, the code displays a message allowing the workbook to be saved, and if not, it displays a message preventing the workbook from being saved. The value of the Cancel parameter is set to True or False, depending on whether the workbook is allowed to be saved or not.

Steps:

  • First, we need to double-click on the worksheet named “LSI” as shown before.
  • Then, we need to write the following code inside the Worksheet.

Code Image of Workbook_BeforeSave Event

Code Syntax:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Do some processing here...
'For example, check if all student scores have been entered
If Sheet5.Range("C5:G14").Find("") Is Nothing Then
'All scores have been entered, allow the workbook to be saved
MsgBox "All student scores have been entered. You can now save the workbook."
Cancel = False
Else
'Not all scores have been entered, prevent the workbook from being saved
MsgBox "Please enter all student scores before saving the workbook."
Cancel = True
End If
End Sub

Code Breakdown:

  • “Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)”: This line defines the Workbook event that triggers the macro before the workbook is saved. It takes two parameters, SaveAsUI and Cancel, which are both Boolean values.
  • “If Sheet5.Range(“C5:G14”).Find(“”) Is Nothing Then”: This line checks if there are any empty cells in the range of student scores in Sheet5. If there are no empty cells, then all student scores have been entered.
  • “MsgBox “All student scores have been entered. You can now save the workbook.””: This line displays a message box notifying the user that all student scores have been entered and the workbook can be saved.
  • “Cancel = False”: This line sets the Cancel parameter to False, which allows the workbook to be saved.
  • “Else”: This line begins the alternative code to execute if there are empty cells in the range of student scores.
  • “MsgBox “Please enter all student scores before saving the workbook.””: This line displays a message box notifying the user that all student scores must be entered before the workbook can be saved.
  • “Cancel = True”: This line sets the Cancel parameter to True, which prevents the workbook from being saved.
  • “End If”: This line ends the conditional statement.
  • “End Sub”: This line ends the macro subroutine.
  • On the sheet named “LSI”, keep as many cells as you want empty inside the table. And try to save the workbook.
  • The workbook will not be saved. And the following MsgBox will be displayed.

Code does not Allow to Save the Workbook

  • Again, if you do not keep any of the cells empty and try to save the workbook, it will be saved and the following MsgBox will be displayed.

Code Allows to Save the Workbook


Things to Remember

  • Target is a reserved keyword in VBA, which means it cannot be used as a variable name.
  • When using Target in an event procedure, it is important to check if the Target cell falls within the expected range. This is done to avoid errors when the user selects or changes cells outside of the expected range.
  • If you are using Target in a Worksheet_Change event, be aware that any changes made to the worksheet within the event procedure can trigger the event again, which can cause an infinite loop. To avoid this, you can temporarily turn off event handling before making any changes to the worksheet and turn it back on afterwards.

Download Practice Workbook

Download the Excel workbook file from the link below to practice.


Conclusion

Using the Target value in VBA can provide several advantages to your Excel automation projects. It can help you to create more dynamic and interactive worksheets, allowing your users to perform actions that trigger specific macros or events. However, it is important to keep in mind some best practices when using the Target value, such as checking if the Target is within the intended range, avoiding infinite loops, and properly handling errors. With the right approach, the Target value can be a powerful tool in your VBA toolkit, allowing you to create more efficient and user-friendly Excel solutions


Related Articles

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:

Md. Nafis Soumik
Md. Nafis Soumik

Md. Nafis Soumik graduated from Bangladesh University of Engineering & Technology, Dhaka, with a BSc.Engg in Naval Architecture & Marine Engineering. In January 2023, he joined Softeko as an Excel and VBA content developer, contributing 50+ articles on topics including Data Analysis, Visualization, Pivot Tables, Power Query, and VBA. Soumik participated in 2 specialized training programs on VBA and Chart & Dashboard designing in Excel. During leisure, he enjoys music, travel, and science documentaries, reflecting a diverse range... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo