MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Worksheet Change event


Posted by Dan on December 17, 2001 10:07 AM

What is the syntax in VBA to have code run if cells in A3 - A65536 are changed. I know how to write it if the change is made in the entire column, but I want to make sure to exclude A1, A2 from the list.

i.e: If Target.Column = 1 then....

TIA


Posted by giacomo on December 17, 2001 10:16 AM

If Target.Column > 1 then....

Posted by Tom Urtis on December 17, 2001 10:21 AM

Maybe you can limit your event range as such:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$3:$A$65536" Then
YOUR CODE
End If
End Sub

Posted by Dan on December 17, 2001 10:36 AM

Tom, didn't seem to work

The code didn't run. Any other thoughts?

: What is the syntax in VBA to have code run if cells in A3 - A65536 are changed. I know how to write it if the change is made in the entire column, but I want to make sure to exclude A1, A2 from the list.

Posted by Dan on December 17, 2001 10:38 AM

I don't want the code to run if Changes are made on cells A1 or A2.

Posted by Tom Urtis on December 17, 2001 10:48 AM

Re: Tom, didn't seem to work

Dan --

Please email me the code you'd like to have triggered along with any explanation, and I'll be happy to test it here. I probably gave you the wrong syntax depending on what you are trying to do.

Thanks.

TomUrtis@attbi.com

The code didn't run. Any other thoughts? : Maybe you can limit your event range as such: : If Target.Address = "$A$3:$A$65536" Then : YOUR CODE : End If : End Sub :

Posted by Juan Pablo G. on December 17, 2001 12:15 PM

Re: Tom, didn't seem to work

Tom, i think the problem is that with your statement the code will only run if all cells in A3:A65536 change at the same time !

How about using this ?

If Target.Row > 2 and Target.Column = 1 then
'Code here...
End If

This will run when ANY cell in Column A from row 3 to 65536 change.

Juan Pablo G. Dan -- Please email me the code you'd like to have triggered along with any explanation, and I'll be happy to test it here. I probably gave you the wrong syntax depending on what you are trying to do. Thanks.

Posted by Dan on December 17, 2001 12:21 PM

That's what I need, thanks Juan!

How about using this ? If Target.Row > 2 and Target.Column = 1 then 'Code here... This will run when ANY cell in Column A from row 3 to 65536 change. Juan Pablo G. : Dan -- : Please email me the code you'd like to have triggered along with any explanation, and I'll be happy to test it here. I probably gave you the wrong syntax depending on what you are trying to do. : Thanks.

Posted by Tom Urtis on December 17, 2001 12:22 PM

Re: Tom, didn't seem to work

Thanks Juan Pablo, in fact he and I emailed off line and that was what I suggested. His code to be triggered was some calculations, and it tested OK on my computer so I think we are all set now.

Thanks again.

Tom Urtis

Tom, i think the problem is that with your statement the code will only run if all cells in A3:A65536 change at the same time ! How about using this ? If Target.Row > 2 and Target.Column = 1 then 'Code here... This will run when ANY cell in Column A from row 3 to 65536 change. Juan Pablo G. : Dan -- : Please email me the code you'd like to have triggered along with any explanation, and I'll be happy to test it here. I probably gave you the wrong syntax depending on what you are trying to do. : Thanks.

Posted by Tom Urtis on December 17, 2001 12:24 PM

Great !! All's well that ends well. Thanks again to both (nt)