Thread: Problem in restoring the hidden rows

Hi,

I am working on a workbook which has two worksheets.
In first worksheet "Solution Design", I have given conditions using drop down and accordingly I want rows to be hidden in other worksheet "Solution Pricing".

Action to do:

If in WS "Solution Design", L9 value = "YES" And L10 value = "Option 1"
Then in WS "Solution Pricing", hide rows Range("J121:J139,J149:J228")

Else If in WS "Solution Design", L9 value = "YES" And L10 value = "Option 2" Then in WS "Solution Pricing", hide rows Range ("J120:J171,J173:J190")

Else If in WS "Solution Design", L9 value = "YES" And L11 value = "Option 1"
Then in WS "Solution Pricing", hide rows Range("J171:J228")

Else If in WS "Solution Design", L9 value = "YES" And L11 value = "Option 2" Then in WS "Solution Pricing", hide rows Range("J120:J171")


I have tried writing a macro for this but unfortunately its not behaving properly. When I change the selection it makes change without unhiding the last hidden rows.
I think I am making a mistake in writing a False statement to unhide.

Below is the code I have tried. Please help in correcting this code.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsEachSheet As Worksheet
    If Intersect(Target, Range("L9")) Is Nothing Then Exit Sub
        If UCase(Range("L9").value) = "YES" And UCase(Range("L10").value) = "Option1" Then
            Worksheets("VIPT_IMS_Rel4").Range("J120:J171,J173:J190, J204:J228").EntireRow.Hidden = False
            Worksheets("VIPT_IMS_Rel4").Range("J171:J228").EntireRow.Hidden = False
            Worksheets("VIPT_IMS_Rel4").Range("J120:J171").EntireRow.Hidden = False
            Worksheets("VIPT_IMS_Rel4").Range("J121:J139,J149:J228").EntireRow.Hidden = True
        ElseIf UCase(Range("L9").value) = "YES" And UCase(Range("L10").value) = "Option2" Then
            Worksheets("VIPT_IMS_Rel4").Range("J121:J139,J149:J228").EntireRow.Hidden = False
            Worksheets("VIPT_IMS_Rel4").Range("J171:J228").EntireRow.Hidden = False
            Worksheets("VIPT_IMS_Rel4").Range("J120:J171").EntireRow.Hidden = False
            Worksheets("VIPT_IMS_Rel4").Range("J120:J171,J173:J190, J204:J228").EntireRow.Hidden = True
        ElseIf UCase(Range("L9").value) = "NO" And UCase(Range("L11").value) = "Option1" Then
            Worksheets("VIPT_IMS_Rel4").Range("J121:J139,J149:J228").EntireRow.Hidden = False
            Worksheets("VIPT_IMS_Rel4").Range("J120:J171,J173:J190, J204:J228").EntireRow.Hidden = False
            Worksheets("VIPT_IMS_Rel4").Range("J120:J171").EntireRow.Hidden = False
            Worksheets("VIPT_IMS_Rel4").Range("J171:J228").EntireRow.Hidden = True
        ElseIf UCase(Range("L9").value) = "NO" And UCase(Range("L11").value) = "Option2" Then
            Worksheets("VIPT_IMS_Rel4").Range("J121:J139,J149:J228").EntireRow.Hidden = False
            Worksheets("VIPT_IMS_Rel4").Range("J120:J171,J173:J190, J204:J228").EntireRow.Hidden = False
            Worksheets("VIPT_IMS_Rel4").Range("J171:J228").EntireRow.Hidden = False
            Worksheets("VIPT_IMS_Rel4").Range("J120:J171").EntireRow.Hidden = True
        End If
End Sub

Br,
gmalpani