run a sub only when one cell changes

[NDBC]

I know i can run private subs based on something like this

Private Sub Worksheet_Change(ByVal Target As Range)

if target.address = "c20" then
.........

But in the name of efficiency is there a way to name a sub so that it is not
even activated unless a specific cell is changed.

Thanks

 

[Rick Rothstein]

Is cell C20 being change by a formula? If so, then we would have

to use the Worksheet_Calculate Event and a memory variable.

That is not necessarily true. Consider this Change event code to monitor if
the value from the formula in C20 has changed...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range
For Each C In Target
If Not Intersect(C.Dependents, Range("C20")) Is Nothing Then
MsgBox "C20 just changed!"
Exit For
End If
Next
End Sub

To test it, put some numbers in, say, A1:A10 and some more numbers in
B1:B10, then put this formula in C20...

=SUM(A1:A10,C1:C10)

Now, change some values in the range A1:A10 or C1:C10