How to use Excel VBA "Worksheet_Calculate" function for a range of cells

Question

I want my macro to activate ONLY when a calculated cell in a SPECIFIED range changes. At the moment the macro activates whenever any cell on the sheet is calculated.

For example, how would I alter the following code so that Macro1 only activates when a cell in Range(A1:A5) changes due to a calculation, and not when cells in any other ranges are recalculated? Any guidance would be much appreciated.

Private Sub Worksheet_Calculate()
    Macro1
End Sub

The hack is to create a new, dedicated sheet, containing only a single populated cell, which references your target cell e.g. `=Sheet1!A435+0` - the Calculate event for that new worksheet would necessarily be scoped to your targeted cell — Spectral Instance, May 06 '22 at 13:38
[This might help.](https://stackoverflow.com/questions/22413877/run-vba-script-when-cell-value-change-by-formula) — Basbadger, May 06 '22 at 15:22

score -1 · Accepted Answer · answered May 06 '22 at 15:57

-1

This should do it...

Private Sub Worksheet_Calculate()
    Static last, test
    
    test = [sum(a1:a5)]
    
    If Not IsError(test) Then
        If last <> test Then
            last = test
            Macro1
        End If
    End If
End Sub

answered May 06 '22 at 15:57

Excel Hero

14,253
4
33
40

You are welcom, Sebastian! – Excel Hero May 08 '22 at 20:58

How to use Excel VBA "Worksheet_Calculate" function for a range of cells

1 Answers1