Combining Worksheet_Change codes

[aashish83]

Is it possible to combine the below? basically need to hide different rows if cells C7, C8, C9 & C10 have yes or no.

Sub Monitor()

Range("A111:A142").EntireRow.Hidden = False

End Sub

Sub Monitor1()

Range("A111:A142").EntireRow.Hidden = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Set Target = Range("C7")

If Target.Value = "Yes" Then

Call Monitor

End If

If Target.Value = "No" Then

Call Monitor1

End If

End Sub



Sub Lock()

Range("A143:A145").EntireRow.Hidden = False

End Sub

Sub Lock1()

Range("A143:A145").EntireRow.Hidden = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)


Set Target = Range("C8")

If Target.Value = "Yes" Then

Call Lock

End If

If Target.Value = "No" Then

Call Lock1

End If

End Sub



Sub Rail()

Range("A146:A152").EntireRow.Hidden = False

End Sub

Sub Rail1()

Range("A146:A152").EntireRow.Hidden = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)


Set Target = Range("C9")

If Target.Value = "Yes" Then

Call Rail

End If

If Target.Value = "No" Then

Call Rail1

End If

End Sub



Sub Vehl()

Range("A153:A159").EntireRow.Hidden = False

End Sub

Sub Veh1()

Range("A153:A159").EntireRow.Hidden = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)


Set Target = Range("C10")

If Target.Value = "Yes" Then

Call Veh

End If

If Target.Value = "No" Then

Call Veh1

End If

End Sub

 

[Fluff]

You can do it like

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("C8:C10")) Is Nothing Then
      Select Case Target.Address(0, 0)
         Case "C7"
            If Target.Value = "Yes" Then
               Call Monitor
            ElseIf Target.Value = "No" Then
               Call Monitor1
            End If
         Case "C8"
            If Target.Value = "Yes" Then
               Call Lock
            ElseIf Target.Value = "No" Then
               Call Lock1
            End If
      End Select
   End If
End Sub

Although you will need to rename your Lock procedure as it's a reserved word.

 

[aashish83]

You can do it like

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("C8:C10")) Is Nothing Then
      Select Case Target.Address(0, 0)
         Case "C7"
            If Target.Value = "Yes" Then
               Call Monitor
            ElseIf Target.Value = "No" Then
               Call Monitor1
            End If
         Case "C8"
            If Target.Value = "Yes" Then
               Call Lock
            ElseIf Target.Value = "No" Then
               Call Lock1
            End If
      End Select
   End If
End Sub

Although you will need to rename your Lock procedure as it's a reserved word.

Thanks Fluff! Will give it a go and revert back with an update

 

[Fluff]

Ok

 

[aashish83]

Ok

Thanks Fluff!!!

Great it worked and changed the Lock to Locking

 

[aashish83]

Thanks Fluff!!!

Great it worked and changed the Lock to Locking

Here is the Final code made some range changes then the original post:

Sub Monitor()

Range("A104:A135").EntireRow.Hidden = False

End Sub
Sub Monitor1()

Range("A104:A135").EntireRow.Hidden = True

End Sub

Sub Locking()

Range("A136:A138").EntireRow.Hidden = False

End Sub

Sub Locking1()

Range("A136:A138").EntireRow.Hidden = True

End Sub
Sub Rail()

Range("A139:A145").EntireRow.Hidden = False

End Sub

Sub Rail1()

Range("A139:A145").EntireRow.Hidden = True

End Sub
Sub Veh()

Range("A146:A152").EntireRow.Hidden = False

End Sub

Sub Veh1()

Range("A146:A1152").EntireRow.Hidden = True

End Sub
Sub IT()

Range("A153:A161").EntireRow.Hidden = False

End Sub

Sub IT1()

Range("A153:A1161").EntireRow.Hidden = True

End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("E4:E8")) Is Nothing Then
      Select Case Target.Address(0, 0)
         Case "E4"
            If Target.Value = "Yes" Then
               Call Monitor
            ElseIf Target.Value = "No" Then
               Call Monitor1
            End If
         Case "E5"
            If Target.Value = "Yes" Then
               Call Locking
            ElseIf Target.Value = "No" Then
               Call Locking1
            End If
            Case "E6"
            If Target.Value = "Yes" Then
               Call Rail
            ElseIf Target.Value = "No" Then
               Call Rail1
            End If
            Case "E7"
            If Target.Value = "Yes" Then
               Call Veh
            ElseIf Target.Value = "No" Then
               Call Veh1
            End If
             Case "E8"
            If Target.Value = "Yes" Then
               Call IT
            ElseIf Target.Value = "No" Then
               Call IT1
            End If
      End Select
   End If
End Sub

 

[Fluff]

You're welcome & thanks for the feedback.