Multiple "Worksheet_Change" routines

[seventy9mph]

here is my code, and I need help either combining the two Worksheet_Change routines or getting another way to do this. Thanks in advance



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$I$2" Then Exit Sub
If Target = "GB" Then
ActiveSheet.Unprotect ("Branches080704")
Application.EnableEvents = False
Range("$F$6:$F$8").ClearContents
Range("$F$4").Locked = False
Range("$F$5:$F$8").Locked = True
Range("$G$6:$G$8").Locked = True
Application.EnableEvents = True
ActiveSheet.Protect ("Branches080704")
Else
If Target.Address <> "$I$2" Then Exit Sub
If Target = "VC" Then
ActiveSheet.Unprotect ("Branches080704")
Application.EnableEvents = False
Range("$F$4").Locked = False
Range("$F$6").Locked = False
Range("$F$7").Locked = False
Range("$F$8").Locked = False
Range("$F$5").Locked = True
Range("$G$6:$G$8").Locked = True
Application.EnableEvents = True
ActiveSheet.Protect ("Branches080704")
Else
If Target.Address <> "$I$2" Then Exit Sub
If Target = "DONE" Then
ActiveSheet.Unprotect ("Branches080704")
Application.EnableEvents = False
Range("$F$4").ClearContents
Range("$F$6:$F$8").ClearContents
Range("$F$4:$G$8").Locked = True
Application.EnableEvents = True
ActiveSheet.Protect ("Branches080704")
Else
End If
End If
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$P$2" Then Exit Sub
If Target = "GB" Then
ActiveSheet.Unprotect ("Branches080704")
Application.EnableEvents = False
Range("$M$6:$M$8").ClearContents
Range("$M$4").Locked = False
Range("$M$5:$M$8").Locked = True
Range("$N$6:$N$8").Locked = True
Application.EnableEvents = True
ActiveSheet.Protect ("Branches080704")
Else
If Target.Address <> "$P$2" Then Exit Sub
If Target = "VC" Then
ActiveSheet.Unprotect ("Branches080704")
Application.EnableEvents = False
Range("$M$4").Locked = False
Range("$M$6").Locked = False
Range("$M$7").Locked = False
Range("$M$8").Locked = False
Range("$M$5").Locked = True
Range("$N$6:$N$8").Locked = True
Application.EnableEvents = True
ActiveSheet.Protect ("Branches080704")
Else
If Target.Address <> "$P$2" Then Exit Sub
If Target = "DONE" Then
ActiveSheet.Unprotect ("Branches080704")
Application.EnableEvents = False
Range("$M$4").ClearContents
Range("$M$6:$M$8").ClearContents
Range("$M$4:$N$8").Locked = True
Application.EnableEvents = True
ActiveSheet.Protect ("Branches080704")
Else
End If
End If
End If
End Sub

 

[Sal Paradise]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim intColumn As Integer
    
    If Intersect(Target, Range("I2"), Range("P2")) Is Nothing Then
        Exit Sub
    End If
                
    Select Case Target.Address
        Case "$I$2"
            intColumn = 5
        Case "$P$2"
            intColumn = 13
    End Select

    Application.EnableEvents = False
    ActiveSheet.Unprotect ("Branches080704")
    
    If Target = "GB" Then
        Cells(intColumn, 6).Resize(3, 1).ClearContents
        Cells(intColumn, 4).Locked = False
        Cells(intColumn, 5).Resize(4, 1).Locked = True
        Cells(intColumn + 1, 6).Resize(3, 1).Locked = True
    ElseIf Target = "VC" Then
        Cells(intColumn, 4).Locked = False
        Cells(intColumn, 6).Resize(3, 1).Locked = False
        Cells(intColumn, 5).Locked = True
        Cells(intColumn + 1, 6).Resize(3, 1).Locked = True
    ElseIf Target = "DONE" Then
        Cells(intColumn, 4).ClearContents
        Cells(intColumn, 6).Resize(3, 1).ClearContents
        Cells(intColumn, 4).Resize(5, 2).Locked = True
    End If
    
    ActiveSheet.Protect ("Branches080704")
    Application.EnableEvents = True

End Sub

Since you're doing the same thing, just with different columns, I simplified it.