This Excel VBA tutorial explains how to use Worksheet.Change Event.
You may also want to read:
VBA Excel Worksheet_Change Event
Excel predefines some popular actions that you would do on different Objects (worksheet, workbook, button, etc), those actions are called Event. For example, activating a worksheet is an Event, closing a workbook is an Event, clicking on a button is an event. Each Object has its own list of Events, Workbook has a list of Events (e.g. close workbook, open workbook), worksheet has a list of Events (e.g. activate worksheet, edit a Cell).
If you perform an Event, say, closing a workbook, your desired code can be triggered. For example, you may want to save a workbook automatically when you close a workbook, or you may want a welcome message box to pop up when a workbook is opened. Event is a Sub Procedure (begin with Private Sub and end with End Sub) and is generated automatically (see in the below section) with a specific name, you can call a Sub Procedure or write your own code within the Event code.
Excel Worksheet_Change Event is an Event triggered when a you leave a Cell from edit mode (even no value is changed). For example, you double click on Cell A1 to enter edit mode, the event is triggered as you press “Enter” or click on any other Cell to exit the edit mode. Excel Worksheet_Change Event is not about value change of a Cell (of course the Event will trigger if you change a value), don’t be misled by the name.
If you want to know how to capture the initial value before change in order to compare the old and new value, read the below article
How to insert Excel Worksheet_Change Event
Like all other worksheet events, you have to define the event and your desired actions within a specific worksheet where you want to Macro to trigger, each worksheet can have its own independent events.
1) Press Alt+F11 to enter into Visual Basic Editor
2) In the Project Explorer Window on the left, double click on the target worksheet
3) On top of the coding area, select “Worksheet” in the drop down box on the left, and then select “Change”.
4) Now you should be able to see two lines of code as below. Insert your action code between the two lines.
Example of Excel Worksheet_Change Event
For example, I want to prompt a message box if column A value >100.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 And Target.Value > 100 Then MsgBox ("Column A value >100") End If End Sub
In the above code, “Target” is the Range you make a change. Target.Column = 1 bounds the checking to column A.
Trigger Macro when Value Changes
Below is a solution I copied and pasted from Microsoft Community that was answered by me.
How can you instruct a macro to run when a certain cell changes?
For example, as soon as text in cell A1 changes, a macro is triggered.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:B10")) Is Nothing Then Dim OldValue As Variant Application.EnableEvents = False Application.Undo OldValue = Target.Value Application.Undo Application.EnableEvents = True If OldValue <> Target.Value Then 'Your Macro End If End If End Sub
Dim oldValue As Variant Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("A1:B10")) Is Nothing Then oldValue = Target.Value End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:B10")) Is Nothing Then If Target.Value <> oldValue Then 'Do something End If End If End Sub