problem with code, if statement

[Sian1]

Hi, hope someone could help me with my code. When workbook first open, all fields are blank, the user be able to have selection options in H10 (Tier1, Tier 2, Tier 3, Tier 4).
let's say the user first select Tier 1 in H10, they are also have 3 options to be chosen in Program Type H13. however, if the user choose Tier 3 or 4, i set default to RT - No Insurance, so far, it is working fine. However, if the user change their mind and would like to switch H10 to either Tier 1 or 2 the default will remain in H13.
my question, how can i make the H13 to clear when the user decided to chose either Tier 1 or 2 from Tier 3 or 4 and be able also have the options to choose in the drop down list (3 options).
I was able to set H13 to clear the content, however whatever they selected in H13 will also be cleared out.

Hope someone could help asap!

Private Sub Worksheet_Change(ByVal Target As Range)
    Static a As Boolean
    Dim myPassword As String
    myPassword = "xxxxxxx"
    ActiveSheet.Unprotect Password:="xxxxxxxxx"
    'MsgBox a
    'MsgBox "start"
'Tier 1 No Re'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If Range("H10").Value = "Tier 1" And Range("H13").Value = "RT - No Reinsurance" Then
        Range("H28:H29").Locked = False
        Range("K10:l23").Locked = False
        Range("K25:l26").Locked = False
        Range("H13").Locked = False
        Range("K24:L24").Locked = True
        a = False
        Rows("39:45").EntireRow.Hidden = True
        Range("H13").Value = ""
'Tier 1 With Re'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
ElseIf Range("H10").Value = "Tier 1" And Range("H13").Value = "RT - With Reinsurance (with Co-Insurance and/or AIG Fronted / Net-Line)" Then
        Rows("39:45").EntireRow.Hidden = False
        Range("H28:H29").Locked = False
        Range("K10:l23").Locked = False
        Range("K25:l26").Locked = False
        Range("H13").Locked = False
        Range("K24:L24").Locked = True
        a = False
        Range("H13").Value = ""
'Tier 1 With Re'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
ElseIf Range("H10").Value = "Tier 1" And Range("H13").Value = "RT - With Reinsurance" Then
        Rows("39:45").EntireRow.Hidden = True
        Range("H28:H29").Locked = False
        Range("K10:l23").Locked = False
        Range("K25:l26").Locked = False
        Range("H13").Locked = False
        Range("K24:L24").Locked = False
        a = False
        Range("H13").Value = ""
'Tier 2 No Re'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
ElseIf Range("H10").Value = "Tier 2" And Range("H13").Value = "RT - No Reinsurance" Then
        Range("H28:H29").Locked = False
        Range("K10:l23").Locked = False
        Range("K25:l26").Locked = False
        Range("H13").Locked = False
        Range("K24:L24").Locked = True
        a = False
        Rows("39:45").EntireRow.Hidden = True
        Range("H13").Value = ""
'Tier 2 With Re'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
ElseIf Range("H10").Value = "Tier 2" And Range("H13").Value = "RT - With Reinsurance (with Co-Insurance and/or AIG Fronted / Net-Line)" Then
        Rows("39:45").EntireRow.Hidden = False
        Range("H28:H29").Locked = False
        Range("K10:l23").Locked = False
        Range("K25:l26").Locked = False
        Range("H13").Locked = False
        Range("K24:L24").Locked = False
        a = False
        Range("H13").Value = ""
'Tier 2 With Re'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
ElseIf Range("H10").Value = "Tier 2" And Range("H13").Value = "RT - With Reinsurance" Then
        Range("H28:H29").Locked = False
        Range("K10:l23").Locked = False
        Range("K25:l26").Locked = False
        Range("H13").Locked = False
        Range("K24:L24").Locked = False
        a = False
        Rows("39:45").EntireRow.Hidden = True
        Range("H13").Value = ""
'Teir 3/4'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
ElseIf Range("H10").Value = "Tier 3" And a = False Then
        a = True
        Range("H28:H29").Locked = True
        Range("K10:l23").Locked = True
        Range("K25:l26").Locked = True
        Range("K24:L24").Locked = True
        Rows("39:45").EntireRow.Hidden = True
        Range("H13").Value = "RT - No Reinsurance"
        'Range("H13").Locked = False
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
ElseIf Range("H10").Value = "Tier 4" And a = False Then
        a = True
        Range("H28:H29").Locked = True
        Range("K10:l23").Locked = True
        Range("K25:l26").Locked = True
        Range("K24:L24").Locked = True
        Rows("39:45").EntireRow.Hidden = True
        Range("H13").Value = "RT - No Reinsurance"
        'Range("H13").Locked = False
End If

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Sian1]

I'm glad to help you. Thanks for the feedback.

Hi,
Hope all is well, im afraid i need to trouble you again. As adding additional conditions the code has broken which you had help me with ( i had tried a few by adding but some works and some doesn't)
i need to have the following done hope you could help me out
a, by default hide rows 42:48
b, if H12="North America" locked cell H32 (applied to all below conditions) (this has trouble me the most! don't know where & how i can fit in the code)
1. if H10= Tier 1 and H14=D
locked cell H13, H15:H20, H23:H32, K11:L28
unhide rows 42:48
2. if (H10= Tier 1 or H10= Tier 2) and H14=A
locked cell H13, K25:L25
hide rows 42:48
3. if (H10= Tier 1 or H10= Tier 2) and H14=C
locked cell H13
unhide rows 42:48
4. if (H10= Tier 3 or H10= Tier 4) and H14=A (A needs to by default) and H11=Casualty and H12=North America
unlocked cell H13
locked cell K11:K17, K25:L27
hide rows 42:48
5. if (H10= Tier 3 or H10= Tier 4) and H14=A (A needs to by default) and H11<>Casualty and H12<>North America
locked cell H13
locked cell K11:K17, K25:L27
hide rows 42:48

here is the code you had written;
Private Sub Worksheet_Change(ByVal Target As Range)
Dim CellRng As Range, myPassword As String, tr1 As String, tr2 As String, tr3 As String, tr4 As String
myPassword = "MNSPT2020"
ActiveSheet.Unprotect Password:="MNSPT2020"

Set CellRng = ActiveSheet.Range("H6")
If Not Intersect(Target, CellRng) Is Nothing Then
If CellRng.Value = vbNullString Then _
CellRng.Formula = "=DATE(YEAR(H5)+1,MONTH(H5),DAY(H5))"

End If

If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("H10, H13")) Is Nothing Then
tr1 = "A"
tr2 = "C"
tr3 = "B"
tr4 = "D"
Rows("39:45").EntireRow.Hidden = True
Select Case Range("H10").Value
Case "Tier 1"
If Range("H13").Value = tr1 Then Call actions(True, False, True, False, "", Target.Address)
If Range("H13").Value = tr2 Then Call actions(False, False, True, False, "", Target.Address)
If Range("H13").Value = tr3 Then Call actions(True, False, False, False, "", Target.Address)
If Range("H13").Value = tr4 Then Call actions(False, False, True, True, "", Target.Address)
Case "Tier 2"
If Range("H13").Value = tr1 Then Call actions(True, False, True, False, "", Target.Address)
If Range("H13").Value = tr2 Then Call actions(False, False, False, False, "", Target.Address)
If Range("H13").Value = tr3 Then Call actions(True, False, False, False, "", Target.Address)
If Range("H13").Value = tr4 Then Call actions(False, False, False, True, "", Target.Address)
Case "Tier 3", "Tier 4": Call actions(True, True, True, False, tr1, Target.Address)
End Select
End If
ActiveSheet.Protect Password:="MNSPT2020"
End Sub

Sub actions(xRows As Boolean, xRng1 As Boolean, xRng2 As Boolean, xRng3 As Boolean, xValue As String, xAdd As String)
Rows("39:45").EntireRow.Hidden = xRows
Range("H28:H29, K10:L23, K25:L26").Locked = xRng1
Range("K24:L24").Locked = xRng2
Range("H16:H35,K10:K27").Locked = xRng3
If xAdd = "$H$10" Then
Application.EnableEvents = False
Range("H13").Value = xValue
Application.EnableEvents = True
End If
End Sub

thanks a millions!