Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:="pass"
If Target.Address(False, False) = "C6" Then
Select Case Target.Value
Case 1: Range("Borrower1_Income").Select
Selection.ClearContents
Range("Borrower2_Income").Select
Selection.ClearContents
Range("Borrower3_Income").Select
Selection.ClearContents
Range("Borrower4_Income").Select
Selection.ClearContents
Range("Borrower5_Income").Select
Selection.ClearContents
Range("Borrower6_Income").Select
Selection.ClearContents
Range("Borrower1_Income").Locked = False
Range("Borrower2_Income").Locked = True
Range("Borrower3_Income").Locked = True
Range("Borrower4_Income").Locked = True
Range("Borrower5_Income").Locked = True
Range("Borrower6_Income").Locked = True
ActiveSheet.Protect Password:="pass", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFiltering:=True
Case 2: Range("Borrower1_Income").Select
Selection.ClearContents
Range("Borrower2_Income").Select
Selection.ClearContents
Range("Borrower3_Income").Select
Selection.ClearContents
Range("Borrower4_Income").Select
Selection.ClearContents
Range("Borrower5_Income").Select
Selection.ClearContents
Range("Borrower6_Income").Select
Selection.ClearContents
Range("Borrower1_Income").Locked = False
Range("Borrower2_Income").Locked = False
Range("Borrower3_Income").Locked = True
Range("Borrower4_Income").Locked = True
Range("Borrower5_Income").Locked = True
Range("Borrower6_Income").Locked = True
ActiveSheet.Protect Password:="pass", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFiltering:=True
Case 3: Range("Borrower1_Income").Select
Selection.ClearContents
Range("Borrower2_Income").Select
Selection.ClearContents
Range("Borrower3_Income").Select
Selection.ClearContents
Range("Borrower4_Income").Select
Selection.ClearContents
Range("Borrower5_Income").Select
Selection.ClearContents
Range("Borrower6_Income").Select
Selection.ClearContents
Range("Borrower1_Income").Locked = False
Range("Borrower2_Income").Locked = False
Range("Borrower3_Income").Locked = False
Range("Borrower4_Income").Locked = True
Range("Borrower5_Income").Locked = True
Range("Borrower6_Income").Locked = True
ActiveSheet.Protect Password:="pass", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFiltering:=True
Case 4: Range("Borrower1_Income").Select
Selection.ClearContents
Range("Borrower2_Income").Select
Selection.ClearContents
Range("Borrower3_Income").Select
Selection.ClearContents
Range("Borrower4_Income").Select
Selection.ClearContents
Range("Borrower5_Income").Select
Selection.ClearContents
Range("Borrower6_Income").Select
Selection.ClearContents
Range("Borrower1_Income").Locked = False
Range("Borrower2_Income").Locked = False
Range("Borrower3_Income").Locked = False
Range("Borrower4_Income").Locked = False
Range("Borrower5_Income").Locked = True
Range("Borrower6_Income").Locked = True
ActiveSheet.Protect Password:="pass", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFiltering:=True
Case 5: Range("Borrower1_Income").Select
Selection.ClearContents
Range("Borrower2_Income").Select
Selection.ClearContents
Range("Borrower3_Income").Select
Selection.ClearContents
Range("Borrower4_Income").Select
Selection.ClearContents
Range("Borrower5_Income").Select
Selection.ClearContents
Range("Borrower6_Income").Select
Selection.ClearContents
Range("Borrower1_Income").Locked = False
Range("Borrower2_Income").Locked = False
Range("Borrower3_Income").Locked = False
Range("Borrower4_Income").Locked = False
Range("Borrower5_Income").Locked = False
Range("Borrower6_Income").Locked = True
ActiveSheet.Protect Password:="pass", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFiltering:=True
Case 6: Range("C15:C16,C19,C21:C33,F15:F16,F19,F21:F33,I15:I16,I19,I21:I33,C39:C40,C43,C45:C57,F39:F40,F43,F45:F57,I39:I40,I43,I45:I57").Select
Selection.ClearContents
Range("Borrower1_Income").Locked = False
Range("Borrower2_Income").Locked = False
Range("Borrower3_Income").Locked = False
Range("Borrower4_Income").Locked = False
Range("Borrower5_Income").Locked = False
Range("Borrower6_Income").Locked = False
ActiveSheet.Protect Password:="pass", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFiltering:=True
Range("a1").Select
End Select
End If
End Sub
Bookmarks