VBA Worksheet_Change to automatically hide row based on cell value

[dropkickweasel]

I'm hoping someone can help me with a hopefully simple VBA issue. I've tried with looking for code online and I just can't get it working, which probably means I'm asking the wrong question or am doing something else wrong.

What I have is an inventory of stock.
As stock is added to the inventory it is placed in a sheet called 'Master'.
In column H of the Master sheet there is a drop down box in each row to select a location for where the stock is stored.
Each location has its own sheet in the workbook, so I can control all stock from the Master sheet, but can select open the "Fridge" sheet to see what stock is in the Fridge.
If stock expires, the location is changed to "Destroyed". There is a Destroyed sheet too, so over time I can see which stock is regularly being destroyed before it is used and can adjust my ordering appropriately.
I want to keep a record of all stock destroyed on the Destroyed sheet.
I do not wish to have the Master sheet clogged up with destroyed stock over time.

So, what I would like to do is to have a way, and I'm guessing it will involve VBA, to hide the rows on the Master sheet when the value in row H is set to "Destroyed" from the drop down box.

I found a piece of code last week that worked perfectly, but after hours of searching through my internet history and trying to replicate the search, I just can't find it again. From memory, it was a single (maybe 2?) lines.
I would like the code to be as stable and robust as possible because I will not be the ultimate end user of the workbook.

Here's the code I'm trying to use at the moment, but it doesn't seem to be working:

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("H2").Value = "Destroyed" Then
Rows("2:2").EntireRow.Hidden = True

End If
End Sub

Row 1 has column headers.
Stock is entered from row 2 onwards.
There will be a varying amount of stock, which will grow over time as Destroyed stock is not deleted from the workbook.
I would like for the code to work for any and every row in which "Destroyed" is selected in column H, and I would like the row to hide immediately when "Destroyed is selected.

I've pasted the above code into the Master sheet code box in VBA (not in a Module), since I only want the code to work on that one sheet.

Can anyone tell me what I'm doing wrong?

Edited to add: I'm using Office365 on Mac, which is what the end-user will be using.

 

[Joe4]

The problem with the code you posted is that it is set to only work on row 2.
To make it work on any row in column H, use this:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit code if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
   
'   See if column H was updated to "Destroyed"
    If Target.Column = 8 And Target = "Destroyed" Then
        Rows(Target.Row).Hidden = True
    End If

End Sub

 

[dropkickweasel]

The problem with the code you posted is that it is set to only work on row 2.
To make it work on any row in column H, use this:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit code if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
  
'   See if column H was updated to "Destroyed"
    If Target.Column = 8 And Target = "Destroyed" Then
        Rows(Target.Row).Hidden = True
    End If

End Sub

Thank you!

I knew it would be something simple and obvious that I was missing.

I had thought that perhaps it would auto-apply to each row because I wasn't using absolute references, but perhaps I always knew that wouldn't work but had to ask for how to make it work.

I've added your code in and it now works perfectly. Thank you!

 

[Joe4]

You are welcome.
Glad I was able to help!