Why doesn't this worksheet level macro work?

[kwp004]

Why doesn't the VBA below work? If range Z1 = "NO", nothing happens. Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)


If Range("Z1") = "NO" Then


 'strikethrough
    
    Range("A1:Q14").Select
    With Selection.Font
    .Strikethrough = True
    End With
    
    Range("G15:K21").Select
    With Selection.Font
    .Strikethrough = True
    End With
    
    Range("A24:Q30").Select
    With Selection.Font
    .Strikethrough = True
    End With
   
    End If
    



If Range("Z1") = "YES" Then
   
     Range("A1:Q14").Select
    With Selection.Font
    .Strikethrough = False
    End With
    
    Range("G15:K21").Select
    With Selection.Font
    .Strikethrough = False
    End With
    
    Range("A24:Q30").Select
    With Selection.Font
    .Strikethrough = False
    End With
    
    
    End If
    
    End Sub

 

[dreid1011]

Does it do anything if Z1 = "YES" ? Also, this probably will not fix the "nothing happens" issue, but you can remove the Select and With Selection portions. Like so:

Private Sub Worksheet_Change(ByVal Target As Range)


If Range("Z1") = "NO" Then


 'strikethrough
    
    
Range("A1:Q14").Font.Strikethrough = True
Range("G15:K21").Font.Strikethrough = True
Range("A24:Q30").Font.Strikethrough = True

   
 End If
    



If Range("Z1") = "YES" Then
   
Range("A1:Q14").Font.Strikethrough = False
Range("G15:K21").Font.Strikethrough = False
Range("A24:Q30").Font.Strikethrough = False    
    
End If
    
End Sub

Edit: This simplified code seems to work as intended, so maybe it did fix it? I will try with your original code and let you know.

 

[dreid1011]

Both versions of code are working for me with the same results.

 

[shg]

You know string comparisons in VBA are case-sensitive by default?

Private Sub Worksheet_Change(ByVal Target As Range)
  Select Case LCase(Range("Z1").Value2)
    Case "no"
      Range("A1:Q14, G15:K21, A24:Q30").Font.Strikethrough = True
    Case "yes"
      Range("A1:Q14, G15:K21, A24:Q30").Font.Strikethrough = False
  End Select
End Sub

And if Z1 is being manually changed, you can preserve Undo for all other cases:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$Z$1" Then
    Select Case LCase(Range("Z1").Value2)
      Case "no"
        Range("A1:Q14, G15:K21, A24:Q30").Font.Strikethrough = True
      Case "yes"
        Range("A1:Q14, G15:K21, A24:Q30").Font.Strikethrough = False
    End Select
  End If
End Sub

 

[shg]

Or, if you'd like a RR one-liner,

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$Z$1" Then Range("A1:Q14, G15:K21, A24:Q30").Font.Strikethrough = Switch(LCase(Range("Z1").Value2) = "no", True, LCase(Range("Z1").Value2) = "yes", False)
End Sub