What is Target Address in Excel
By
SpreadCheaters
In MS Excel, Target is the name assigned to the Range object variable that is passed on as the argument of Worksheet Object Events in case of events like Worksheet_Change or Worksheet_SelectionChange. Once we have the Target as Range Object we can then find the address of the range where the event has happened. Let’s see a practical example and learn how to use this to our advantage.
Consider the sheet shown below. We have a drop down list to choose the first names of the players. We will use the Target.Address property along with the Worksheet_Change event to identify the location of the cell where the change actually happened. If the change has occurred in the cell of interest, which in our case will be $I$2, then we’ll perform some activities like making the font bold and changing the font colour as well.
Microsoft Excel is a very powerful tool for data analysis and mathematical calculations. The power of this software is compounded when VBA (Visual Basic for Applications) is used along with it. VBA adds more functionality and the feature of automating tasks to Excel through macros. Macros are the small code snippets that can be written using Visual Basic for Applications.
Step 1 – Save the File as Macro Enabled Workbook
– Now to run the macros and use the Target.Address property, first save the file as Macro Enabled Workbook.
– For this, click on the File menu, click on SaveAs and save the file with the .xlsm extension. This will make the workbook to run the macros when required.
Step 2 – Open VBA Editor
– The VBA code has to be used to enable the multiple selection drop down list. To do this open the VBA code editor by pressing ALT+F11 keys simultaneously.
– Double click the Sheet’s Name in the project window. This will open up a white space in the right panel.
Step 3 – Add an event listener to any change in the sheet
– Now we’ll add an event listener to our project by changing the General option to Worksheet in the Object selection drop down.
– Then select the Change event in the Procedure drop down list. It will add the following piece of code into the code writing area automatically.
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
Step 4 – Enter the code snippet to use Target.Address
– The VBA code to use Target.Address is very simple. We’ll add the code inside the event listener function which is Private Sub Worksheet_Change(ByVal Target As Range). The code snippet is given at the end of this tutorial. You may copy and paste it in your worksheet.
Step 5 – Check the implementation of Target.Address
– After entering the VBA code, we can now test the working of this code by going to the sheet and changing the values in different cells and then changing the value in the cell where the drop down list exists. The font will become bold and the colour of the font will be changed when we change anything in cell I2.
We can see that when we make any change in the cell other than I2, no change occurred in the font colour or font weight of the drop down list. However, when we changed the value in cell I2, then the font weight and font colour also changed as desired.
