IF statment nested within VBA Code

[deger]

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

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

PS i have no idea why emojis are replacing my text, when i edit and change to text, the emojis appear again?

Firstly, there are a few issues in your code:

In the Set changed = Intersect(Target, Range("C3")) line, you're using Range("C3"), but you mentioned you want to check cell C4. You should change that line to Set changed = Intersect(Target, Range("C4")).

In your Select Case Target.Value statements, you are checking ActiveWorkbook.Sheets("Rate Calc").Range("$C$4").Value, which is always checking the value of cell C4 on the "Rate Calc" sheet. If you want to check the value in cell C4 of the current worksheet, you should replace it with Range("C4").Value.

Here's an updated version of your code with these corrections:

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("C4"))
    
    If Not changed Is Nothing Then
        Select Case Target.Value
            Case "Air"
                ' Your code for hiding/unhiding rows for "Air" goes here
            Case "Ocean_Asia_to_EU"
                ' Your code for hiding/unhiding rows for "Ocean_Asia_to_EU" goes here
            Case "Shanghai to Genoa"
                ' Your code for hiding/unhiding rows for "Shanghai to Genoa" goes here
            Case "Overland"
                ' Your code for hiding/unhiding rows for "Overland" goes here
        End Select
    End If
End Sub

You can now add additional Case statements under the Select Case structure for other conditions you want to check in cell C4 and adjust the code accordingly for each case.


Please make sure to replace ' Your code for hiding/unhiding rows for ... goes here with your actual code for hiding/unhiding rows based on the condition in cell C4.