How to hide and unhide based on drop down list in VBA?

[VBAEXCELNew]

Hi, everyone

i am in a scenario where currently i want to unhide row 19 - 22 based on my drop down list. I wan to show my row 19-22 when i select in this drop down "Dumping/Destruction" or "Donation To Charity" other wise my row 19-22 will be hide, how i can based on this write in my vba on changes and when selecting this drop down a message box appear to prompt the user?

 

[VBAEXCELNew]

I have written a simple vba script however i have mismatch issue can anyone help me out why ?
I have a column J where i wan from j6 to whatever j last row is how can i do it ?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim keycells As Range
Set keycells = Range("J6:J999")

If Not Application.Intersect(keycells, Range(Target.Address)) Is Nothing Then
If keycells = "Dumping / Destruction" Then
If keycells = "Donation To Charity" Then
Rows("19:22").Hidden = False
Else
Rows("19:22").Hidden = True
End If
End If
End If

   


End Sub

 

[nemmi69]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim keycells As Range
Dim KLoop As Range
Set keycells = Range("J6:J999")
For Each KLoop In keycells
    If Not Application.Intersect(keycells, Range(Target.Address)) Is Nothing Then
        If KLoop.Value = "Dumping / Destruction" Or KLoop.Value = "Donation To Charity" Then
            Rows("19:22").Hidden = False
            exit for
        Else
            Rows("19:22").Hidden = True
        End If
    End If
Next

End Sub

 

[VBAEXCELNew]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim keycells As Range
Dim KLoop As Range
Set keycells = Range("J6:J999")
For Each KLoop In keycells
    If Not Application.Intersect(keycells, Range(Target.Address)) Is Nothing Then
        If KLoop.Value = "Dumping / Destruction" Or KLoop.Value = "Donation To Charity" Then
            Rows("19:22").Hidden = False
            exit for
        Else
            Rows("19:22").Hidden = True
        End If
    End If
Next

End Sub

Thanks, but i have issue when reselect the dropdown the hidden row wouldnt appear again

 

[nemmi69]

Its because you are checking a range. Some of them are true some false so it will switch

 

[VBAEXCELNew]

Its because you are checking a range. Some of them are true some false so it will switch

oh i would like to check based on cell by cell on J6 onwards

 

[nemmi69]

My bad, try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim keycells As Range
Set keycells = Range("J6:J999")
If Not Application.Intersect(keycells, Range(Target.Address)) Is Nothing Then
    If Target.Value = "Dumping / Destruction" Or Target.Value = "Donation To Charity" Then
        Rows("19:22").Hidden = False
    Else
        Rows("19:22").Hidden = True
    End If
End If

End Sub

 

[VBAEXCELNew]

My bad, try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim keycells As Range
Set keycells = Range("J6:J999")
If Not Application.Intersect(keycells, Range(Target.Address)) Is Nothing Then
    If Target.Value = "Dumping / Destruction" Or Target.Value = "Donation To Charity" Then
        Rows("19:22").Hidden = False
    Else
        Rows("19:22").Hidden = True
    End If
End If

End Sub

Thanks it work, since we are at here do u know when i select this target.value how do we make a message pop out to prompt the user ?
i realize why the row not hiding is due to my drop down list there some spelling or capital letter different LOL

 

[nemmi69]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim keycells As Range
Set keycells = Range("J6:J999")
If Not Application.Intersect(keycells, Range(Target.Address)) Is Nothing Then
    If Target.Value = "Dumping / Destruction" Or Target.Value = "Donation To Charity" Then
        Rows("19:22").Hidden = False
        MsgBox "Please select from bla bla", vbInformation, ThisWorkbook.Name
    Else
        Rows("19:22").Hidden = True
    End If
End If

End Sub