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]