Case statement - hide/unhide based on conditional

Question

I'm trying to hide a column if the case value is no.

I saved this in the sheet, not a module.

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.Activate

    If Not Application.Intersect(Range("C9"), Range(Target.Address)) Is     Nothing Then
        Select Case Target.Value
           Case Is = "Yes":
              Rows("10:10").EntireRow.Hidden = False
           Case Is = "No":
              Rows("10:10").EntireRow.Hidden = True
        End Select

    End If
End Sub

Answer 1

Worksheet Calculate Event

• Workbook Download (Dropbox)
• If Yes and No are being changed by a formula, the Worksheet Change event will not be triggered when the value changes.
• Copy the first code into a standard module, e.g. Module1 (VBE >> Insert >> Module).
• Copy the second code into a sheet module, e.g. Sheet1.
• Copy the third code into ThisWorkbook module.

Module1

Option Explicit

Public strYesNo As String

Sub YesNo(CellRange As Range, HideRow As Long)

    Const str1 As String = "Yes"
    Const str2 As String = "No"

    With CellRange
        If .Value <> strYesNo Then
            Select Case strYesNo
                Case str1
                    .Worksheet.Rows(HideRow).Hidden = False
                Case str2
                    .Worksheet.Rows(HideRow).Hidden = True
            End Select
            strYesNo = .Value
        End If
    End With

End Sub

Sub YesNo1()

    Const cSheet As Variant = "Sheet1"
    Const cRange As String = "C9"
    Const cCol As Long = 10

    YesNo ThisWorkbook.Worksheets(cSheet).Range(cRange), cCol

End Sub

Sheet1

Option Explicit

Private Sub Worksheet_Calculate()
    YesNo1
End Sub

ThisWorkbook

Option Explicit

Private Sub Workbook_Open()
    YesNo1
End Sub

Answer 2

Maybe it's related to a case-sensitive fail check. Try this code and tell us if this solve the issue:

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.Activate

    If Not Application.Intersect(Range("C9"), Range(Target.Address)) Is Nothing Then
        Select Case LCase(Target.Value)
            Case Is = "yes":
                Rows("10:10").EntireRow.Hidden = False
            Case Is = "no":
                Rows("10:10").EntireRow.Hidden = True
        End Select
    End If

End Sub

---