MrExcel.com - Your One Stop for Excel Tips & Solutions

Macro When Excel Cell Changes

June 25, 2002 - by

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

/p> 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