On Error Resume Next
'site
With Sheets("site").Columns(4).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=tmpl!$A:$A"
End With
Sheets("site").range("D1:D6").Validation.Delete
具体方法的使用请参考MSDN
联动demo
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tarVal As String
tarVal = Target.Value
If tarVal <> "" And Target.Column = 1 Then
Debug.Print "a"
Debug.Print Target.Row
Debug.Print Target.Column
Debug.Print Sheet2.Range("a:a").Find(Target.Value).Address
Debug.Print Sheet2.Cells(Sheet2.Range("a:a").Find(Target.Value).Row, 2).Value
With Sheets("sheet1").Cells(Target.Row, 2).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Replace(Sheet2.Cells(Sheet2.Range("a:a").Find(Target.Value).Row, 2).Value, ";", ",")
End With
End If
End Sub