How does it do with a few changes below:
In the code above the RED parts relate to C3 dropdown, the BLUE parts relate to C4 dropdownPrivate Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Address = "$C$3" Then Range("C4").Value = "Please Select Origin..." Application.EnableEvents = True Exit Sub ElseIf Target.Address = "$C$4" Then ActiveSheet.Unprotect Password:="dlm" Select Case Target.Offset(-1).Value Case "Air" Range("A10:A19").EntireRow.Hidden = True Range("A39:A43").EntireRow.Hidden = True Range("A56:A58").EntireRow.Hidden = True If Target.Value = "Warsaw to New York" Then Range("A23").EntireRow.Hidden = True End If Case "Ocean_Asia_to_EU" Range("A8:A15").EntireRow.Hidden = True If Target.Value = "Shanghai to Genoa" Then Range("A23").EntireRow.Hidden = True Range("A57").EntireRow.Hidden = True Range("A51:A74").EntireRow.Hidden = True End If Case "Shanghai to Genoa" Range("A17:A19").EntireRow.Hidden = True Range("A8:A9").EntireRow.Hidden = False Range("A12").EntireRow.Hidden = False Range("A14").EntireRow.Hidden = True Range("A15").EntireRow.Hidden = False Range("A11").EntireRow.Hidden = True Range("A12").EntireRow.Hidden = True Range("A13:A15").EntireRow.Hidden = True Range("C15:D15").ClearContents Range("C14:D14").ClearContents Range("D17:D19").ClearContents Range("C8:D8").ClearContents Range("C9:D9").ClearContents Case "Overland" Range("A8:A9").EntireRow.Hidden = True Range("A7:A12").EntireRow.Hidden = True Range("A15").EntireRow.Hidden = True Range("A14").EntireRow.Hidden = False Range("A18:A19").EntireRow.Hidden = True Range("A17").EntireRow.Hidden = False Range("A13:A15").EntireRow.Hidden = True Range("C11:D11").ClearContents Range("C12:D12").ClearContents Range("C13:D13").ClearContents Range("C14:D14").ClearContents Range("C15:D15").ClearContents Range("C17:D17").ClearContents Range("C18:D18").ClearContents Range("C19:D19").ClearContents End Select ActiveSheet.Protect Password:="dlm" Range("C3").Select End If Application.EnableEvents = True End Sub




Reply With Quote