Unhide Another Row if Range contains text Value

[Newbie_Reena]

Hi there! I'm new to VBA so please be gentle.

In my Excel sheet I have dropdowns that range from G20 through Z20.

I'd like to keep Row 21 hidden unless the word "Upgrade" appears anywhere in the range G20 to Z20, then i want Row 21 to be unhidden. If the user changes the dropdown and Upgrade is no longer anywhere in that range again, Row 21 goes back to hidden. Can anyone help with the VBA code? I tried to record the macro but it won't stop recording, nor will it do what I want.

Appreciate the help!

 

[Newbie_Reena]

I've tried this but not working:

Sub ShowUpgrade(ByVal Target As Range)
'
' DefineInput Macro
'


Rows("21:21").EntireRow.Hidden = True
If Target.Row = 20 And Target.Column > 6 And Target.Value = "Upgrade" Then
Rows("21:21").EntireRow.Hidden = False
End If


End Sub

 

[mikerickson]

try putting this in the worksheet's code module

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("G20:Z10")) Is Nothing Then
        Rows(21).EntireRow.Hidden = (Application.CountIf(Range("G20:Z20"), "Upgragde") = 0)
    End If
End Sub

 

[Newbie_Reena]

Thanks! I tried that and inserted it as a new Module. It's not working. Row 21 never unhides no matter what is inputted into that range. Is there something else I should be doing?

 

[mikerickson]

That is event code. I doesn't go in a new module. In the Project explorer window, double click on the sheet in question and paste that code into the window that appears.

 

[Newbie_Reena]

Thank you! That worked!