VBA to hide rows when a cell is a specific value and then hide the associated worksheet

[dh1996]

Hello, very new to VBA so apologies if this is a very simple answer! I have an excel sheet (made by someone else) that runs a macro that gives a new row a number and creates a new worksheet with the same number as you'll see in the image. In column J the 'Status' column there's a drop down list where you can choose a few different options. I'm trying to make a script (by fudging together other peoples examples online) that when the status is 'Closed' the whole row hides but it also hides the worksheet with the associated number. So far i've had no luck, i've tried removing the worksheet part and just focusing on hiding the rows but i can't even get that any help would be greatly appreciated.

Sub worksheet_change(ByVal target As Range)

    Set target = Range("J5:J10")

    If target.Text = "Closed" Then

        Dim i As Integer

        For i = 5 To 10

            If Cells(10, i).Text = "Closed" Then
    
                Cells(10, i).EntireRow.Hidden = True
    
        Else
    
                Cells(10, i).EntireRow.Hidden = False
    
        End If
    
        Next i

    End If

Dim wkSht As Worksheet

For Each wkSht In Sheets

    For Each Cell In Sheets("Issue List").Range("A5:A10")

        If Rows("5:10").EntireRow.Hidden = True & Cell.Value = wkSht.Name Then
            
            wkSht.Hidden = True
            
            End If
        
        Next Cell
    
    Next wkSht

End Sub

 

[NoSparks]

I believe this is what you're after.
This is Sheet Event code.
Right click on the sheet tab --> View Code
Paste this in the Sheet module when it opens.
Target is the cell that changed.

Private Sub Worksheet_Change(ByVal Target As Range)
   
    ' limit to a single cell
    If Target.CountLarge > 1 Then Exit Sub
   
    Dim rng As Range
    Dim wkSht As String     ' the sheet name
   
    ' set the range to monitor for change
    Set rng = Range("J5:J10")
   
    ' is the changed cell within rng
    If Not Intersect(Target, rng) Is Nothing Then
        ' Yes, if it wasn't it would be nothing
        If Target.Value = "Closed" Then
            wkSht = Cells(Target.Row, "A").Value
            Target.EntireRow.Hidden = True
            Sheets(wkSht).Visible = False
        End If
    End If

End Sub

 

[dh1996]

I believe this is what you're after.
This is Sheet Event code.
Right click on the sheet tab --> View Code
Paste this in the Sheet module when it opens.
Target is the cell that changed.

Private Sub Worksheet_Change(ByVal Target As Range)
  
    ' limit to a single cell
    If Target.CountLarge > 1 Then Exit Sub
  
    Dim rng As Range
    Dim wkSht As String     ' the sheet name
  
    ' set the range to monitor for change
    Set rng = Range("J5:J10")
  
    ' is the changed cell within rng
    If Not Intersect(Target, rng) Is Nothing Then
        ' Yes, if it wasn't it would be nothing
        If Target.Value = "Closed" Then
            wkSht = Cells(Target.Row, "A").Value
            Target.EntireRow.Hidden = True
            Sheets(wkSht).Visible = False
        End If
    End If

End Sub

That works perfectly! thank you so much for the help! definitely need to learn how to do this sorta stuff myself

 

[NoSparks]

You're welcome.
Hope the comments in the code help with the learning process.