Hey guys, I am trying to write for a worksheet change event for two non contiguous range , which are cells A&lastrow and C&lastrow. For example, if lastrow=5, then it is A5, and C5, while excluding B5. This is my code, and it is not working, any idea on how to fix the syntax.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastrow As Long
lastrow = 5
If Not Intersect(Target, Range("A & lastrow, C & lastrow ")) Is Nothing Then
MsgBox "Hello"
End If
End Sub
Please Login or Register to view this content.
Ben Van Johnson
Doesn't work. This is non contiguous range ,meaning B5 is to be excluded, your codes identify B5 as well .
Does this do what you want?
Please Login or Register to view this content.
Hi Andy, nope doesn't solve it.
You need to provide more useful feed back.
For me, if I select A5 and C5 and enter values in both cells at once using CTRL+ENTER it works.
If I have both selected but only enter value into 1 cell, using Enter, then it does not work.
If I select only 1 of the cells and enter it does not work.
If I select more cells it does not work.
Please explain the circumstances under which it should work
Maybe it is just a very simple case, like :
Please Login or Register to view this content.
1. I care dog
2. I am a loop maniac
3. Forum rules link : Click here
3.33. Don't forget to mark the thread as solved, this is important
Andy, I have made it very clear on my question. Non Contiguous. Should be understood that one of either A5 or C5 will be inputted, the worksheet change event will be fired.
To me, non contiguous means a reference to 2 or more cells.
You simply mean 2 non adjacent cells
yes Andy.
I have found the solution . Its as follows
if not intersect(target,union(range("A" & lastrow),range("D"&lastrow))) is nothing then msgbox "hello"
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks