Hi
Try this sheet change event
Private Sub Worksheet_Change(ByVal Target As Range)
If LCase(Target) = "yes" Then
Application.EnableEvents = False
With Range("C5:C11,c13:c19").Validation
Range("C5:C11,c13:c19") = "Please select"
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="5,7"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Application.EnableEvents = True
End If
If Not Intersect(Target, Range("C5:C11,c13:c19")) Is Nothing And LCase(Range("C3")) = "yes" Then
Application.EnableEvents = False
Range("C24:C30").Value = Range("C5:C11").Value
Range("C43:C49").Value = Range("C5:C11").Value
Range("C32:C38").Value = Range("C13:C19").Value
Range("C51:C57").Value = Range("C13:c19").Value
Application.EnableEvents = True
End If
If LCase(Target) = "no" Then
Application.EnableEvents = False
With Range("C5:C11,c13:c19,c24:c30,c32:c38,c43:c49,c51:c57").Validation
Range("C5:C11,c13:c19,c24:c30,c32:c38,c43:c49,c51:c57") = "Please select"
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="5,7"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Application.EnableEvents = True
End If
End Sub
Bookmarks