vba设置动态的数据有效性

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)'单元格选择发生变化时，触发该事件，执行事件下的代码
If Target.Column = 1 And Target.Count = 1 And Target.Row > 1 Then 'Target表示该单元格
With Target.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="主机,显示器"
End With
End If
End Sub
'-------------------------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)'单元格的值发生变化时，触发该事件，执行事件下的代码，上面的事件发生变化时，这个事件也会随之变化
If Target.Column = 1 And Target.Row > 1 And Target.Count = 1 Then 'Target表示该单元格
With Target.Offset(0, 1).Validation
.Delete
Select Case Target
Case "主机"
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="Z286,Z386,Z486,Z586"
Case "显示器"
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="15,17,21,25"
End Select
End With
End If
End Sub