Macro for Hiding two rows if Cell has specific statement?

[donggri84]

Hello,

I've been looking online to see what macro I can use for when:

e.g.
B8 = "New Zealand" and "Select"

Hide Rows 22 and 23

otherwise keep it unhidden or If B8 = "Australia", unhidden

Could anyone possibly help me?

Thank you so kindly,
It would be a huge help!
Kind regards,

 

[bewsh1987]

TRY

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Err:
If Application.Intersect(Target, Range("B8")) = "New Zealand" Then
    Rows("22:23").EntireRow.Hidden = True
End If

If Application.Intersect(Target, Range("B8")) = "Australia" Then
    Rows("22:23").EntireRow.Hidden = False
End If
Err:

End Sub

 

[AlphaFrog]

• Right-click on the sheet tab
• Select View Code from the pop-up context menu
• Paste the code from below in the worksheet's code module.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]If[/COLOR] Target.Address(0, 0) = "B8" [COLOR=darkblue]Then[/COLOR]
        Rows("22:23").Hidden = (Target.Value <> "New Zealand" And Target.Value <> "Select")
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

 

[donggri84]

TRY

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Err:
If Application.Intersect(Target, Range("B8")) = "New Zealand" Then
    Rows("22:23").EntireRow.Hidden = True
End If

If Application.Intersect(Target, Range("B8")) = "Australia" Then
    Rows("22:23").EntireRow.Hidden = False
End If
Err:

End Sub

Thank you so much!
Worked great :D

Kind regards

 

[donggri84]

Thank you it works well! it was other way around but I can work it out.
Thank you so so much for your kind help!
Regards,
D

 

[donggri84]

Hello again,

I just realized Macro does not work if the sheet is password protected.
I'm not quite sure where I should be adding below statement if I have to?

ActiveSheet.Unprotect "abc"


Thank you again
Regards
D