Using Worksheet Change Event To Run Macro When any Change is Made

If you are here, its probably you want to learn how to run a subroutine every time a change is made in a specific sheet. This article will teach exactly that. So let's get started.

The Syntax for the Worksheet Change Event Handler is:

Private Sub worksheet_change(ByVal Target As Range)
'your code
'
'
End Sub

This code must be written in a worksheet object in order to work, not in a normal module.

Private Sub worksheet_change(ByVal Target As Range)
 Msgbox "You updated something in this sheet"
End Sub

If you have this code on sheet1 and you write or delete something in the sheet1, the massage will pop that "You updated something in this sheet".

It won't work in normal modules. But you can call subroutines from any module. We will do the same in this session.

Scenario:

Run Excel Macro if Any Change is Made on Worksheet

So, let's say we have a macro that changes the color of cells based on some condition. Just like Conditional Formating of Excel. The only difference is that the color of cells changes when we run this VBA code using a button or run command, not when the values change. We want the macro to run whenever a change is made in the sheet. Here VBA Events will help. Now to do this we will use Worksheet Change Event.

Follow these steps:

Go to project explorer and double click on the sheet on which you want to trigger the worksheet.change event. I want to trigger the event when some changes made on the Sheet2, so I double click on the Sheet2. You can also right-click and click on the view code to open the code writing area for sheet objects.In the code area, write this line of code.
```
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub
```
worksheet_change(Target as Range) is a preserved subroutine that runs when a change is made on the code containing sheet.

When you will write this code, you will see the object changing to the worksheet. See the upper-left drop-down. In the upper-right drop-down, the event is "change".
If you use the drop-down and select the worksheet in the left drop-down, all the available events will be shown in the right dropdown. Whichever drop-down you choose, the code for that event will be written automatically. You just need to fill it with your custom subroutines.
Now just put your code in this segment. I want to call my subroutine that is in a module. So I simply call that subroutine in this worksheet event handler. My subroutine is FormatUsingVBA() in Module1
```
Private Sub worksheet_change(ByVal Target As Range)
 Call FormatUsingVBA
End Sub
```

Now, whenever we will make a change in sheet2 the macro FromatUsingVBA will run.

So yeah guys, this how can make run a VBA script when a sheet changes its content, using Worksheet Change Event handler. I hope it was helpful. If you have any queries regarding this event handler, mention it in the comments section.

Related Articles:

Simplest VBA Code to Highlight Current Row and Column Using | Use this small VBA snippet to highlight the current row and column of the sheet.

The Worksheet Events in Excel VBA | The worksheet event are really useful when you want your macros run when a specified event occurs on the sheet.

Run Macro If Any Change Made on Sheet in Specified Range | To run your macro code when the value in a specified range changes, use this VBA code. It detects any change made in the specified range and will fire the event.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need filter your data to count specific value. Countif function is essential to prepare your dashboard.

How to Use the SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.