Hide columns and multiple sheets using loop

Question

I currently have a loop that works great to hide columns based on multiple dropdown cells. I would also like to add code to hide sheets based on the same drop downs, but I'm not sure how to add on to my For Each Cell In Range to accommodate that. I have pasted what I have to hide the columns below. Any help would be greatly appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

For Each cell In Range("$A$30:$A$38")

   If cell = "Descriptor 1" Or cell = "Descriptor 2" Then
    Columns("B:F").EntireColumn.Hidden = False
   Exit For
   Else
    Columns("B:F").EntireColumn.Hidden = True
End If
Next Cell

Answer 1

You can use something like Worksheets("sheet_to_hide").Visible = xlSheetHidden to hide a sheet and Worksheets("sheet_to_unhide").Visible = xlSheetVisible to unhide it again.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    Dim HideIt As Boolean

    HideIt = True
    For Each cell In Range("$A$30:$A$38")    
       If cell.Value = "Descriptor 1" Or _
          cell.Value = "Descriptor 2" Then
           HideIt = False
           Exit For
       End If
    Next Cell

    If HideIt Then
        Columns("B:F").Hidden = True
        Worksheets("Sheet1").Visible = xlSheetHidden
        Worksheets("Sheet2").Visible = xlSheetHidden
    Else
        Columns("B:F").Hidden = False
        Worksheets("Sheet1").Visible = xlSheetVisible
        Worksheets("Sheet2").Visible = xlSheetVisible
    End If
End Sub

---

If the worksheets are to be hidden / made visible depending on whether their sheet name appears in your range, then I would suggest the following modification:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    Dim HideIt As Boolean

    'Don't do anything if there was no change to A30:A38
    If Intersect(Target, Range("$A$30:$A$38")) Is Nothing Then Exit Sub

    HideIt = True
    For Each cell In Range("$A$30:$A$38")
       If cell.Value = "Descriptor 1" Or _
          cell.Value = "Descriptor 2" Then
           HideIt = False
           Exit For
       End If
    Next cell
    Columns("B:F").Hidden = HideIt

    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Name <> ActiveSheet.Name Then
            'See if sheet name exists in A30:A38
            'Hide the sheet if doesn't, make it visible if it does
            ws.Visible = Not IsError(Application.Match(ws.Name, Range("$A$30:$A$38"), 0))
        End If
    Next
End Sub