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

 

[stanleydgromjr]

NDBC,

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

I do not think so.


Is cell C20 being changed manually or by a formula?

If it is being changed manually, then see below "*Manually:"*.


Is cell C20 being change by a formula? If so, then we would have t
use the Worksheet_Calculate Event and a memory variable.

Please post your workbook - scroll down and see "Manage Attachments".



MANUALLY
Right click the sheet tab you want the code in, and click on View Code
Paste the below code there (on the right pane).


Code
-------------------


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C20")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False


'The rest of your code goes here


Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub


-------------------




Have a great day,
Sta

 

[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