|
发表于 2016-4-29 15:52
|
显示全部楼层
本楼为最佳答案
本帖最后由 cabcd1 于 2016-4-29 15:56 编辑
安全网 发表于 2016-4-29 11:10
如果是E列使用的数据是SHEET2表内的的B列,SHEET2表内的的A列和B列的数据是不同的,该怎么设置 - Private Sub Worksheet_Change(ByVal Target As Range)
- Dim n As Integer
- Dim rg
- Dim str As String
- n = Sheet2.Range("A65536").End(xlUp).Row
- str = ""
- If Target.Column = 4 And 3 <= Target.Row <= 65536 Then
- If Target <> "" Then
- For Each rg In Sheet2.Range("A1:A" & n)
- If rg.Value Like "*" & Target.Value & "*" Then
- str = str & "," & rg.Value
- End If
- Next rg
- With Target.Validation
- .Delete
- If str = "" Then Exit Sub
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
- xlBetween, Formula1:=str
- .IgnoreBlank = True
- .InCellDropdown = True
- .IMEMode = xlIMEModeNoControl
- .ShowInput = True
- .ShowError = False
- End With
- Else
- Target.Validation.Delete
- End If
- End If
- If Target.Column = 5 And 3 <= Target.Row <= 65536 Then
- If Target <> "" Then
- For Each rg In Sheet2.Range("B1:B" & n)
- If rg.Value Like "*" & Target.Value & "*" Then
- str = str & "," & rg.Value
- End If
- Next rg
- With Target.Validation
- .Delete
- If str = "" Then Exit Sub
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
- xlBetween, Formula1:=str
- .IgnoreBlank = True
- .InCellDropdown = True
- .IMEMode = xlIMEModeNoControl
- .ShowInput = True
- .ShowError = False
- End With
- Else
- Target.Validation.Delete
- End If
- End If
- End Sub
复制代码 复制一下修改Target.Column = 5 Sheet2.Range("B1:B" & n)
|
|