Hi Paul,
This worked a treat, I can't thank you enough
I've added an extra line to make the Rate Calc sheet look cleaner until the lane has been selected, it basically stop/hides the 'False' being shown because the user hasn't selected their lane yet from C4 cell..
If Target.Address = "$C$3" Then
Range("C4").Value = "Please Select Origin..."
My only stumbling block now is I need to be able to hide an 'Active xControl check box' when a certain lane is selected , namely the below, it fails to hide or unhide the check boxes, any ideas?
Case "Ocean_EU_to_US" & "#" & "Genoa to New York" Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Range("A54:A57").EntireRow.Hidden = True
ActiveSheet.CheckBox1.Visible = True
ActiveSheet.CheckBox2.Visible = True
ActiveSheet.CheckBox3.Visible = True
ActiveSheet.CheckBox4.Visible = True
ActiveSheet.CheckBox5.Visible = True
ActiveSheet.CheckBox6.Visible = True
Case "Ocean_EU_to_US" & "#" & "Genoa to Los Angeles"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Range("A54:A57").EntireRow.Hidden = True
ActiveSheet.CheckBox1.Visible = False
ActiveSheet.CheckBox2.Visible = False
ActiveSheet.CheckBox3.Visible = False
ActiveSheet.CheckBox4.Visible = False
ActiveSheet.CheckBox5.Visible = False
ActiveSheet.CheckBox6.Visible = False
Full code is below
Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range, rC3 As Range, rC4 As Range
Set r = Target.Cells(1, 1) 'only check first cell
Set rC3 = Range("C3")
Set rC4 = Range("C4")
If r.Address <> rC3.Address And r.Address <> rC4.Address Then Exit Sub
If Len(r.Value) = 0 Then Exit Sub
If Target.Address = "$C$3" Then
Range("C4").Value = "Please Select Origin..."
End If
Application.EnableEvents = False
Unprotect Password:="dlm"
Select Case rC3.Value & "#" & rC4.Value
Case "Air" & "#" & "Warsaw to New York"
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = False
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True
Case "Air" & "#" & "Warsaw to Los Angeles"
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = False
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True
Case "Air" & "#" & "Malpensa to New York"
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = True
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True
Case "Air" & "#" & "Malpensa to Los Angeles"
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = False
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True
Case "Air" & "#" & "Heathrow to New York"
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = True
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True
Case "Air" & "#" & "Heathrow to Los Angeles"
Range("A6").EntireRow.Hidden = True
Range("A8").EntireRow.Hidden = False
Range("A9:A11").EntireRow.Hidden = False
Range("A12:A21").EntireRow.Hidden = True
Range("A23:A25").EntireRow.Hidden = True
Case "Ocean_EU_to_US" & "#" & "Genoa to New York"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Range("A54:A57").EntireRow.Hidden = True
ActiveSheet.CheckBox1.Visible = True
ActiveSheet.CheckBox2.Visible = True
ActiveSheet.CheckBox3.Visible = True
ActiveSheet.CheckBox4.Visible = True
ActiveSheet.CheckBox5.Visible = True
ActiveSheet.CheckBox6.Visible = True
Case "Ocean_EU_to_US" & "#" & "Genoa to Los Angeles"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Range("A54:A57").EntireRow.Hidden = True
ActiveSheet.CheckBox1.Visible = False
ActiveSheet.CheckBox2.Visible = False
ActiveSheet.CheckBox3.Visible = False
ActiveSheet.CheckBox4.Visible = False
ActiveSheet.CheckBox5.Visible = False
ActiveSheet.CheckBox6.Visible = False
Case "Ocean_EU_to_US" & "#" & "Genoa/La Spezia to Los Angeles"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Case "Ocean_EU_to_US" & "#" & "Gdynia to New York"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Case "Ocean_EU_to_US" & "#" & "Gdynia to Los Angeles"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Case "Ocean_EU_to_US" & "#" & "Hamburg to New York"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Case "Ocean_EU_to_US" & "#" & "Hamburg to Los Angeles"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Case "Ocean_EU_to_US" & "#" & "FXT/SOU to New York"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Case "Ocean_EU_to_US" & "#" & "FXT/SOU to Los Angeles"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Case "Ocean_Asia_to_EU" & "#" & "Shanghai to Genoa"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Case "Ocean_Asia_to_EU" & "#" & "Xiamen to Genoa"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Case "Ocean_Asia_to_EU" & "#" & "Shanghai to Gdynia/Gdansk"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Case "Ocean_Asia_to_EU" & "#" & "Xiamen to Gdynia/Gdansk"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Case "Ocean_Asia_to_EU" & "#" & "Shanghai to FXT/SOU"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Case "Ocean_Asia_to_EU" & "#" & "Xiamen to FXT/SOU"
Range("A51:A74").EntireRow.Hidden = False
Range("A23:A25").EntireRow.Hidden = False
Case "Overland" & "#" & "PL to UK"
Range("A8:A13").EntireRow.Hidden = True
Range("A14").EntireRow.Hidden = False
Range("A15:A21").EntireRow.Hidden = True
Range("A38:A74").EntireRow.Hidden = True
Range("A24:A25").EntireRow.Hidden = True
Case "Overland" & "#" & "UK to PL"
Range("A8:A13").EntireRow.Hidden = True
Range("A14").EntireRow.Hidden = False
Range("A15:A21").EntireRow.Hidden = True
Range("A38:A74").EntireRow.Hidden = True
Range("A24:A25").EntireRow.Hidden = True
End Select
ActiveSheet.Protect Password:="dlm"
Application.EnableEvents = True
End Sub