Worksheet Change Sub - trying to use target.range

[gmail2]

I'm trying to write a sub worksheet_Change sub which will run certain code if a certain cell or range of cells are changed. The range is K11:K65000 - so I have the following

Private Sub Worksheet_Change(ByVal target As Range)

If target.Range(K11, K65000) Then
MsgBox ("it worked")
End If

End Sub

Obviously the msgbox is just for debugging. But I get an error message saying Applicaton-defined or object-defined error (whatever that means ). What am I doing wrong, how do I refer to the range of cells?

Irish Poetry - Karen O'Connor
Get your Irish Poetry Published
Garten und Landschaftsbau

 

[rmikesmith]

Try this instead:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = $K$11:$K$65000" Then
  MsgBox ("it worked")
End If

End Sub

Regards,
Mike

 

[Loomah]

Isn't this what's really needed

If Not Intersect(Target, Range("K11:k65000")) Is Nothing Then
  MsgBox ("it worked")
End If

Happy Friedegg!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[rmikesmith]

Loomah,

Happy Friedegg to you!

Yes, you pegged it. For some reason (probably related to cerebral deficiency), that was the line I was looking for but couldn't put my finger on.


Regards,
Mike

 

[Loomah]

probably related to cerebral deficiency

I thought I'd got that quarantined (there's a word I'm sick of at the moment!) on planet Loomah!!

Woooo-Hoooo - Only 63 hours to Monday morning

Hippy Friarday

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​