Run a Macro Every Time a Cell Value Changes in Excel
Several readers have asked questions which require Excel to run a section of macro every time a value changes in the Excel spreadsheet.
First, the improved method available only in XL97: Excel 97 has some new event handlers that allow a macro to be run every time a cell changes.
Let's say that anytime a value greater than 100 is entered in column A, you want to format the cell next to it to be red.
- Open the Visual Basic Edit (Tools>Macro>Visual Basic Editor)
- In the left window, right click Sheet1 and select View Code.
- At the top of the Book1 - Sheet1 Code dialog box, there are two dropdowns. From the left dropdown select Worksheet. From the right dropdown, select Change.
- Enter the following lines of code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column = 1 Then ThisRow = Target.Row If Target.Value > 100 Then Range("B" & ThisRow).Interior.ColorIndex = 3 Else Range("B" & ThisRow).Interior.ColorIndex = xlColorIndexNone End If End If End Sub
Any time a value in a cell is changed, this macro will be run. The variable Target will tell you which cell and the new value of the cell. Surprisingly, using this method does not significantly slow down the process.
Note that the macro will stay active as long as the worksheet is open or until you run a macro with the following line in it:
Application.EnableEvents = False
In Excel 95/7.0: You need to use the OnEntry method. You specify a macro that you want to be run after any value is entered. In this case, the variable Application.Caller contains the address and value that changed. Enter the following in a new module:
Sub AutoOpen() Worksheets("Sheet1").OnEntry = "CheckIt" End Sub Sub CheckIt() If Application.Caller.Column = 1 Then ThisRow = Application.Caller.Row If Application.Caller.Value > 100 Then Range("B" & ThisRow).Interior.ColorIndex = 3 Else Range("B" & ThisRow).Interior.ColorIndex = xlColorIndexNone End If End If End Sub
OnEntry checking will stay active until you run a macro with the following code:
Worksheets("Sheet1").OnEntry = False