Search code examples
excelvbaautomation

automatically execute an Excel macro on a cell change

How can I automatically execute an Excel macro each time a value in a particular cell changes?

Right now, my working code is:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("H5")) Is Nothing Then Macro
End Sub

where "H5" is the particular cell being monitored and Macro is the name of the macro.

Is there a better way?

Solution

  • Your code looks pretty good.

    Be careful, however, for your call to Range("H5") is a shortcut command to Application.Range("H5"), which is equivalent to Application.ActiveSheet.Range("H5"). This could be fine, if the only changes are user-changes -- which is the most typical -- but it is possible for the worksheet's cell values to change when it is not the active sheet via programmatic changes, e.g. VBA.

    With this in mind, I would utilize Target.Worksheet.Range("H5"):

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("H5")) Is Nothing Then Macro
End Sub

    Or you can use Me.Range("H5"), if the event handler is on the code page for the worksheet in question (it usually is):

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("H5")) Is Nothing Then Macro
End Sub