automatically execute an Excel macro on a cell change

Question

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?

Accepted Answer

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
```

automatically execute an Excel macro on a cell change

Solution

Problem

Related problems