How to make sub handle more than 1 cell ?

[Guest]

Hi, I have this code for Worksheet_Change event which will lock & gray out
the cell in column Q if a value is entered into column P & vice-versa. How do
I modify the code below to be able to handle more than 1 cell at a time ?

Example: If P23 & P24 currently are empty & I paste in numbers into these
two cells, then Q23 & Q24 would gray out & become locked. Inversely, if I
select P23 & P24 which is filled and press the delete key to clear its
contents, then Q23 & Q24 would un-gray itself (become yellow in this case) &
become unlocked.

Secondly, how do I change the code to detect if the selection overlaps and
just execute for the relevant column ? Example: If I select Q23:R24 (where
Q23 & Q24 are filled) and I press the delete key thereby clearing contents
for that selection, then it will trigger P23 & P24 cells to unlock & un-gray
itself. I keep getting vba error when I test out this kind of action.

Any help appreciated. TIA.

Here's the code :

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Not Intersect(Range("P"), Target) Is Nothing Then

ActiveSheet.Unprotect "123456"

If ((IsEmpty(Target.Value) = False) And (IsNull(Target.Value) = False))
Then


Target.Offset(0, 1).Value = ""
Target.Offset(0, 1).Interior.ColorIndex = 16

Target.Offset(0, 1).Locked = True


If (Target.Offset(0, -10).Value = "A") Then
MsgBox "Please enter comments."
End If

If (Target.Offset(0, -10).Value = "B") And (Target.Offset(0, -9).Value >
60) Then
MsgBox "Please enter comments."
End If

Else

Target.Offset(0, 1).Locked = False
Target.Offset(0, 1).Interior.ColorIndex = 36


End If

ActiveSheet.Protect Password:="123456"

End If

If Not Intersect(Range("Q:Q"), Target) Is Nothing Then

ActiveSheet.Unprotect "123456"

If ((IsEmpty(Target.Value) = False) And (IsNull(Target.Value) = False))
Then

Target.Offset(0, -1).Value = ""
Target.Offset(0, -1).Interior.ColorIndex = 16

Target.Offset(0, -1).Locked = True

If (Target.Offset(0, -11).Value = "A") Then
MsgBox "Please enter comments."
End If

If (Target.Offset(0, -11).Value = "B") And (Target.Offset(0, -10).Value >
60) Then
MsgBox "Please enter comments."
End If

Else
Target.Offset(0, -1).Locked = False
Target.Offset(0, -1).Interior.ColorIndex = 36

End If

ActiveSheet.Protect Password:="123456"

End If

Application.EnableEvents = True

End Sub

 

[Guest]

Don't believe you can do that with Worksheet_Change. That seems to be for a
single cell target. You would have to write code in either a Case or If
....Then statement to handle more than one cell at a time and it would have to
be evaluated after it is entered, rather than as it is entered.

 

[Guest]

Here is a site you might want to read to get a better understanding of the
Worksheet_Change event.

http://www.cpearson.com/excel/Events.aspx

 

[Guest]

Hi, thanks for your explanation & the link. If it's not possible to do what I
want with Worksheet_Change, could you help to show me how to handle the
exceptions..... i.e. so the code will only run when only 1 cell is selected ?

TIA

 

[Guest]

Hi, thanks for your explanation & the link. If it's not possible to do what I
want with Worksheet_Change, could you help to show me how to handle the
exceptions..... i.e. so the code will only run when only 1 cell is selected ?

TIA