Hello everyone, Noob Scuba here
I have only just began to dabble with VBA code and have created the below code, which works perfectly for me, until such time as I introduce some extra IF statements and then it breaks, so I'm looking for some help please, if possible.
I have an excel spreadsheet (its a rate calculator) and I would the existing code below to still run as is but also be able to insert additional if statements under each 'CASE' headings as I need to hide or unhide certain rows depending on what text is inserted into cell C4 of the excel sheet.
I'm using Office 365.
Hopefully some here can help me understand what I'm doing wrong.
Thanks
Richard
PS i have no idea why emojis are replacing my text, when i edit and change to text, the emojis appear again?Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$3" Then Range("C4").Value = "Please Select Origin..." End If Dim changed As Range Set changed = Intersect(Target, Range("C3")) If Not changed Is Nothing Then Select Case Target.Value Case "Air" ActiveSheet.Unprotect Password:="dlm" Range("A10:A19").EntireRow.Hidden = True Range("A39:A43").EntireRow.Hidden = True Range("A56:A58").EntireRow.Hidden = True If ActiveWorkbook.Sheets("Rate Calc").Range("$C$4").Value = ("Warsaw to New York") Then Range("A23").EntireRow.Hidden = True ActiveSheet.Protect Password:="dlm" Case "Ocean_Asia_to_EU" ActiveSheet.Unprotect Password:="dlm" Range("A8:A15").EntireRow.Hidden = True If ActiveWorkbook.Sheets("Rate Calc").Range("$C$4").Value = ("Shanghai to Genoa") Then Range("A23").EntireRow.Hidden = True Range("A57").EntireRow.Hidden = True Range("A51:A74").EntireRow.Hidden = True ActiveSheet.Protect Password:="dlm" Case "Shanghai to Genoa" ActiveSheet.Unprotect Password:="dlm" 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 ActiveSheet.Protect Password:="dlm" Case "Overland" ActiveSheet.Unprotect Password:="dlm" 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("D17:D19").ClearContents ActiveSheet.Protect Password:="dlm" End Select Range("C3").Select End If End Sub





Reply With Quote