Streamline TargetAddress code

[ChristineJ]

How can the code below be consolidated so that when a cell in B4:B5, B8:B18, B21:B27, B30:B32, B31:B39, B41:B43, and B46:B50 is selected, the word "No" appears in column C in the same row? Thanks!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$B$4" Then
Range("C4").Value = "No"
End If

If Target.Address = "$B$5" Then
Range("C5").Value = "No"
End If

If Target.Address = "$B$8" Then
Range("C8").Value = "No"
End If

If Target.Address = "$B$9" Then
Range("C9").Value = "No"
End If

If Target.Address = "$B$10" Then
Range("C10").Value = "No"
End If

If Target.Address = "$B$11" Then
Range("C11").Value = "No"
End If

End Sub

 

[Fluff]

How about

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("B4:B5, B8:B18, B21:B27, B30:B32, B31:B39, B41:B43,B46:B50")) Is Nothing Then
      Target.Offset(, 1).Value = "No"
   End If
End Sub

 

[ChristineJ]

How about

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("B4:B5, B8:B18, B21:B27, B30:B32, B31:B39, B41:B43,B46:B50")) Is Nothing Then
      Target.Offset(, 1).Value = "No"
   End If
End Sub

Thanks! Works perfectly! CJ

 

[Fluff]

You're welcome & thanks for the feedback.