IF statment nested within VBA Code

**Scuba** · 09-05-2023, 02:12 AM

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?

**Aussiebear** · 09-05-2023, 04:24 AM

Welcome to VBAX Richard. It an unfortunate thing within Excel that Smileys invade our text, formula and code. Microsoft have much to answer for in this aspect.

In relation to your code,

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

I'm struggling to understand the logic of your intention here. From your code I can only assume that the sheet is password protected, so how does one select cell C3? If cell C3 is not the target what then was the value of cell C4? This type of code should be something like this

Please note that in VBA, all "If" statements are bound by "If" and "End If". You have a number of "If" statements that have no "End If", which is non logical from a VBA point of view.

**Scuba** · 09-05-2023, 04:52 AM

Hi,
Firstly thank you for you quick answer and second apologies for my rudimentary coding.
I'm still very new to VBA and learning by the day

This code works alongside excel, I have 2 drop cells )first one (c3) and 2nd one C4, which is conditional on what has been selected from C3, when the users selects from a drop down cell (C3), I auto automictically insert the value 'Please Select Origin...' into C4, because if I did not do this a lot other cells in excel show 'False' because the formulas can't calculate as the 2nd conditional drop down has not been selected ie cell (C4).
the issue I'm having is everything works fine if don't add in additional IF statements, but the second I do nothing works, this worksheet has evolved now from the original where I could just hide and unhide specific rows base don the value in C3, i now need to add additional criteria to hide extra rows and also Active X check boxes but one step at a time, I would be happy to just be able to hide and unhide specific rows base of a value in C4, I then work on the check boxes afterwards.

I hope I'm making sense, sorry if I'm not, in summary I have a set of criteria that runs when C3 is selected, then under each heading that the user selects from C3 I need to run additional criteria based of cell C4 ie just like a conditional drop works

**Aflatoon** · 09-05-2023, 07:53 AM

Originally Posted by Aussiebear

Please note that in VBA, all "If" statements are bound by "If" and "End If".

That is not actually true. You can have single line If ... Then ... statements that do not require an End If. So this is perfectly valid syntax:

            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"

but the blue part is the only part that is contingent on the If statement. The three lines that follow are only contingent on the original Case statement.

**Scuba** · 09-05-2023, 08:09 AM

Understood, so how do you make so both parts are work together, i.e. the Case statement runs and then the additional If statements within each Case statement run as well.
I only shows you 2 additional IF statements, I have more but just showed 2 because if I can get those 2 to work, I can probably work out how to get the rest working

**June7** · 09-05-2023, 08:29 AM

if you mean there were emojis in your posted code, it was probably because you did not initially post code between CODE tags. Aussiebear edited your post to correct this. The forum will substitute certain character sequences for emojis unless you go to Advanced Editor and turn this off. Turn off is only for that edit session. Have to do it every time. Has nothing to do with MS or Excel or VBA. The takeaway here is to post code between CODE tags.

**Scuba** · 09-05-2023, 08:35 AM

Sorry, very new to this forum and full rules etc, I'll learn, my questions were not relating to the way this forum changes my code when copying but how to get my example VBA to work correctly

**June7** · 09-05-2023, 08:46 AM

i know that but you still needed to know how to correctly post code and why you got emojis.

Are you saying the two IIf() statements cause failure? What does "failure" mean - error message, wrong result, nothing happens?

Might provide your workbook for analysis. Follow instructions at bottom of my post.

**Aflatoon** · 09-05-2023, 08:59 AM

If all of those lines should be part of the If test, then it does require an End If as it is no longer a single line construction:

            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"
End If

**Scuba** · 09-05-2023, 09:03 AM

thanks VBAS Master, I'll try to add in some 'End If's' and see how I get one, I'm about to log off for the day but I'll be back on tomorrow, thanks for all your help everyone

**Aussiebear** · 09-05-2023, 01:56 PM

Okay, now that I've had 4 hours of sleep, lets deal with a couple of issues.

Firstly @ June7, Emojis can and often do occur outside of the "forum". One can be constructing a document in word and using sub paragraphs denoted by (a), (b), © etc and the emojis will appear. it is definitely a MS thing, and one they have not fixed.

@Afltoon, After having a second thoughts about your statement, I agree, I was wrong, however as the code is currently presented by Richard, I am inclined to believe that there were a number of conditional statements attached to the "If" occurring on another sheet namely "Rate Calc". On that basis I believe you require an "End If" to enable returning to the Active sheet to continue the Case Select process. I may well be wrong, but that is my reading from the code as presented in Post #1.

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

It's unclear to me, whether the ActiveSheet and ActiveWorkbook.Sheets('Rate Calc") are one and the same or different. If they are diferent sheets then this begs the question, on which sheet are we trying to hide the rows A23 and A51:A74?

@ Scuba, As I currently understand your ActiveSheet has two dropdown cells namely C3 and C4. Cell C3 has 4 options namely, "Air", "Ocean_Asia to EU", "Shanghai to Genoa" and "Overland", and you desire certain actions to take place depending on which option has been selected. The relevance of the message in cell C4 is still unclear to me. Is it because you are asking the user to select an option in cell C3 or C4? If its to select an option in cell C3 then a simple Messagebox would suffice.

Lets look at the actions undertaken in the first option of the case select, namely "Air"

I believe you could write this as ;

        Case "Air"
            ActiveSheet.Unprotect Password:="dlm"
            Range ("A10:A19, A39:A43, A56:A58").EntireRow.Hidden = True _
            .....  
           ActiceSheet.Protect.Password:= "dlm"

Now comes the misleading bit. Can you clarify if the Activesheet and ActiveWorkbook.Sheets("Rate Calc") are different or the same sheet? In post #1 you suggested the cell C4 has a value "Please select Origin...". Did you intend for "Warsaw to New York" to be one of the options for the dropdown in cell C4? If it was an option then it messes with lines 2 and 3 of your original code as presented.

            If ActiveWorkbook.Sheets("Rate Calc").Range("$C$4").Value = "Warsaw to New York" Then _
            Range("A23").EntireRow.Hidden = True

Then we look at the case select option "Ocean_Asia to EU"

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"

This gets back to the point raised by Aftloon, as to whether ranges A51:A74 are part of the If statement or part of the conditions of the Case Select option. Also the line "Range("A57").entireRow.Hidden = true" is unnecessary since in the next line you have included it within the range A51:A74 as hidden.

In the next select option "Shanghai to Genoa", you have conflicting objectives.

        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"

You are both hiding and unhiding certain rows, such as A12 for example. Which do you need it to be hidden or unhidden? Next you are attempting to Clear Contents of Rows that are hidden. What purpose does this serve?

Then we look at the next Select option "Overland"

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"

Again you are over complicating things here by suggesting to hide rows which are then included within the next lines grouping to be hidden. Further down you suggest that Row A14 is not hidden but three lines down you surest its part of a group of lines that are hidden. Then again further down in the code you are clearing the contents of rows that are hidden from the User. What is the intent of doing this?

Can you either attach a sample workbook for us to see what you are attempting to complete, or failing that, please write out in plain English the logic flow of what you would like to happen please?

**deger** · 09-05-2023, 11:33 PM

Originally Posted by Scuba

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.

**Scuba** · 09-06-2023, 01:54 AM

Hello Aussiebear,

Glad you had some sleep, although 4 hours is not long really lol

I'll do my best to answer all your point as clearly as possible

Confirming there are some IF statements on the rate Calc sheet and that ActiveSheet and ActiveWorkbook.Sheets('Rate Calc") are one and the same, that's just my ham fisted way of trying to get it to work for me, sorry my

bad.

Cell 3 is my primary drop down list and consists of the below:

Air
Ocean_EU_to_US
Ocean_Asia_to_EU
Overland

Cell C4 is my Secondary drop down and is dependent on what is selected from the primary dropdown (C3)

Air
Warsaw to New York
Warsaw to Los Angeles
Malpensa to New York
Malpensa to Los Angeles
Heathrow to New York
Heathrow to Los Angeles

Ocean_EU_to_US

Genoa to New York

Genoa/La Spezia to Los Angeles

Gdynia to New York

Gdynia to Los Angeles

Hamburg to New York

Hamburg to Los Angeles

FXT/SOU to New York

FXT/SOU to Los Angeles

Ocean_Asia_to_EU
Shanghai to Genoa
Xiamen to Genoa
Shanghai to Gdynia/Gdansk
Xiamen to Gdynia/Gdansk
Shanghai to SOU/FXT
Xiamen to SOU/FXT

Overland
PL to UK
UK to PL

I insert the value '"Please select Origin..' into cell C4 so its a prompt to tell the user to select from that drop down, it also serves another purpose in that, if that is displayed in C4 then my Rate Calc fields are all blank otherwise I get odd data appearing in the main rate calc sheet.

I am bastardising this Rate Calc from another customer of mine and so some of the actual lines that are showing hidden/unhidden/clear etc will be incorrect, this I'm not concerned with as I will clean this up once I understand how to incorporate the Case statements alongside the additional IF statements.

There will be some cross over between what is asked to be hidden and what is asked to be shown, based on what is selected in the primary drop down C3 and then further hidden/reveal requests when the secondary drop list activated.
for example when Air is selected I don't need to show rows 23 to 25 as these are container information that is only used for Ocean movements.

If its easier i can share the actual document, once i work out how to attach it to this forum, i looked at all the icons but couldn't see once for attaching something, only for inserting an image or a video, no documents

Once again thank you for you help and patience

**Scuba** · 09-06-2023, 01:57 AM

Hi Deger,

Thank you for your guidance, I will attempt to try this and will revert if I get any issues.

However when I think about this, I don't think this will work, unless I am misunderstanding something here(which is possible lol).

The Case values are referring to cell C3, whereas the IF statements will be referring the cell C4 so not sure if this change will work as Case Air = cell C3 and IF statements will = Cell C4 so when using Case "Air" wont it fail as we have said 'Set changed = Intersect(Target, Range("C4"))'?

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

Once again thank you and I am so glad I have chanced across this forum, it is really great.

regards

Richard

**Aussiebear** · 09-06-2023, 04:47 AM

Welcome to VBAX Deger, Thank you for your contribution, for it's nice to see new contributors. Just a couple of quick housekeeping hints, we try to wrap any supplied code with code tags. Please refer to the first line in my signature for a hint. Also it's not necessary to fully quote a response when making a post. I know they do it on some of the other forums, but here we take the view that it simply is a waste of whitespace. That aside, please keep up the great work.

**Aussiebear** · 09-06-2023, 04:57 AM

@Scuba, click on Go Advanced, Manage Attachments, choose file, and Upload to attach a workbook. Make a note of the file size limits.

From your indications in post #13, there's probably no need to have Cell C4 reflect any message as its dependant dropdown depending on what has been selected in Cell C3. Other than that follow the advice offered by Deger, and workout clearly what needs to hidden or unhidden. BTW the same housekeeping advice go to you as well, please wrap your code with code tags.

**Scuba** · 09-06-2023, 05:36 AM

Noted and understood, file uploaded

**Scuba** · 09-06-2023, 06:00 AM

Stupid noob question, but what and where do I find 'Code Tags'?

**georgiboy** · 09-06-2023, 06:01 AM

How does it do with a few changes below:

Private 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

In the code above the RED parts relate to C3 dropdown, the BLUE parts relate to C4 dropdown

**georgiboy** · 09-06-2023, 06:03 AM

Originally Posted by Scuba

Stupid noob question, but what and where do I find 'Code Tags'?

When you are writing the post, it is the # symbol in the mini ribbon

Thread: IF statment nested within VBA Code

Thread Tools

Display

IF statment nested within VBA Code

Posting Permissions