How To Run A Macro When Cell Value Changes In Excel VBA

Sometimes you want to be able to monitor a range of cells in your worksheet for any changes. Using this, you can run a macro when a cell changes to a specific value.

Here I will show how to run a macro when a cell value changes in Excel VBA

This post will also answer the question you may have, “How do I automatically trigger a macro in Excel?” When the cell value changes, we will trigger a macro to run.

In the following example, I am monitoring the range “A1:A4” (blue shading) for changes.

I want the user to be made aware when the value of the cell changes and what the previous value of the cell was. That way I can write code to have excel vba undo the cell change .

Here’s the code

Dim m_dblOldValue As Double


Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rngMonitor As Range
    Dim rngCell As Range
    
    'monitor this range of cells
    Set rngMonitor = Range("A1:A4")
    
    '*********************************************
    'was one of the values in the range changed?
    '*********************************************
    
    'loop through each cell in the range...
    For Each rngCell In rngMonitor
        'if the current cell address matches one of the cells to monitor, flag it with a message
        
        If Target.Address = rngCell.Address Then
           'I get the previous value of the cell from the "SelectionChange" event
            MsgBox Target.Address & " changed from " & m_dblOldValue & " to " & Target.Value
        End If
    Next
    
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Get the previous value of the changed cell
    m_dblOldValue = Target.Value
End Sub

In the “Worksheet_SelectionChange” event, I can get the previous value.
In the “Worksheet_WorksheetChange” event, I can monitor the cell changes.

Watch me do it:

Let me know if you have any questions.

Use this form to send me a message:

[simple_contact_form]