How to combine two Worksheet_Change subs into one?

[olivier5b]

Hi everyone,

I need help to combine the two following subs that work fine separately into one that works. Your help would be appreciated. Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B9")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Range("B19,B27").ClearContents
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B11")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Range("E16,E27").ClearContents
End Sub

 

[pgc01]

Hi Olivier

Try:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("B9")) Is Nothing Then
    Range("B19,B27").ClearContents
End If

If Not Intersect(Target, Range("B11")) Is Nothing Then
    Range("E16,E27").ClearContents
End If

End Sub

 

[hiker95]

olivier5b,


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 12/26/2012
' http://www.mrexcel.com/forum/excel-questions/676322-how-combine-two-worksheet_change-subs-into-one.html#post3350133
If Intersect(Target, Range("B9,B11")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Row = 9 Then
  Range("B19,B27").ClearContents
ElseIf Target.Row = 11 Then
  Range("E16,E27").ClearContents
End If
End Sub

 

[shg]

Another way:

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address
        Case "$B$9"
            Range("B19,B27").ClearContents
        Case "$B$11"
            Range("E16,E27").ClearContents
    End Select
End Sub

 

[olivier5b]

Thanks shg, hiker95, pgc01 - I tried all 3 solutions and they all work. Very helpful, I really appreciate your time!

 

[shg]

You're welcome.

 

[hiker95]

olivier5b,

You are very welcome. Glad we could help.

Thanks for the feedback.

Come back anytime.