Run Macro on Change

[Guest]

I have the following code in Sheet1. When I change data in cell D5, it
invokes the FindInv macro. If I then try to clear a range of cells in Sheet1,
say D34, the code below runs again and I get a type mismatch error. What do
I need to do to prevent this?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("D5") Then FindInv
End Sub


Thanks.

 

[Guest]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.address = "$D$5" Then FindInv
End Sub

 

[Guest]

Thanks, Jim.
This is my first foray into this type of VBA code. I'm moving from plain
vanilla to vanilla swirl.....

 

[Guest]

Stick around... Tom might give out his recipe for Chocolate... I'm still
waiting...

 

[Guest]

Jim,
Maybe you can make it chocolate by telling him what his first attempt was
actually checking for.

 

[Guest]

I bet your chocolate tastes better but here goes...

The default property of a range such as Target or Range("D5") is the value
of the range, so the macro would run not only when D5 was changed, but
whenever the cell that was changed had a value equal to the value in D5...
What we need to check in this case is the address of the cell that was
changed, so by checking the address of the target (returns a string) against
"$D$5" we will know when cell D5 changed...

One day Tom I will be half the chef you are and then we will all be in
trouble...