Hide or unhide sheet depending on cell value

Question

I am looking to write a code about hiding or un-hiding worksheets in Excel depending the value of a cell.

I have reached to this

Sub Hide_Un()
    If Range("b4").Value = "yes" Then
        sheets(2).Visible = True
    ElseIf Range("b4").Value = "no" Then
        sheets(2).Visible = False
    End If

    If Range("b5").Value = "yes" Then
        sheets(3).Visible = True
    ElseIf Range("b5").Value = "no" Then
        sheets(3).Visible = False
    End If

There are about 100 sheets and I can't do this procedure every time I add a new sheet.

I need a code to hide or unhide each worksheet depending my declaration to a cell.

Example B1="yes" (visible) or B1="no" (not visible)

Answer 1

1. Option Compare Text makes this non case sensitive. This way YES = yes. Without this Option, they would not be equal
2. You may need to account for the option of the value being neither yes or no. What if value is y or n or yes with a lagging space?
3. Using the sheet index number (Sheet(n)) can be problematic if users have the option to add/move/delete sheets in the book.
4. It looks like the row number relates to sheet number by Sheet # = Row -2. I am starting the loop at 4. The lowest this could be is 3 - other wise you will end up trying to hide a sheet that cannot exist

---

Option Explicit
Option Compare Text

Sub Hide_Un()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("TOC")
Dim i

Application.ScreenUpdating = False
    For i = 4 To ws.Range("B" & ws.Rows.Count).End(xlUp).Row
        If ws.Range("B" & i) = "yes" Then
            ThisWorkbook.Sheets(i - 2).Visible = xlSheetVisible
        ElseIf ws.Range("B" & i) = "no" Then
            ThisWorkbook.Sheets(i - 2).Visible = xlSheetHidden
        Else 'What if the cell is neither?
            'Do what you want if the cell is not "yes" or "no"
        End If
    Next i
Application.ScreenUpdating = True

End Sub

Answer 2

Put this code into the ThisWorkbook code sheet.

Option Explicit    

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Select Case LCase(Sh.Name)
        Case "toc"
            If Not Intersect(Target, Sh.Range("B:B")) Is Nothing Then
                Dim t As Range
                For Each t In Intersect(Target, Sh.Range("B:B"))
                    If t.Row > 3 Then
                        Worksheets(t.Row - 2).Visible = _
                            CBool(LCase(t.Value) = "yes")
                    End If
                Next t
            End If
        Case Else
            'do nothing
    End Select

End Sub