在这个Sheet里面加上如下代码: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count = 1 Then If Target.Column = 10 And (Target.Row > 4 And Target.Row < 11) Then Dim strYS$, YS1$, YS2$, ArrYS2, i&, strAdd1$, strAdd2$, lngTemp& strYS = Cells(Target.Row, 8) If Len(strYS) > 0 Then YS1 = Join(Application.Transpose(Range("A2:A" & Range("A65536").End(xlUp).Row)), ",") ArrYS2 = Application.Transpose(Range("C2:C" & Range("A65536").End(xlUp).Row)) YS2 = Join(ArrYS2, ",") If InStr(1, YS1, strYS, vbTextCompare) > 0 And InStr(1, YS2, strYS, vbTextCompare) Then lngTemp = WorksheetFunction.Match(strYS, ArrYS2, 0) strAdd1 = "$C$" & lngTemp + 2 For i = lngTemp + 1 To UBound(ArrYS2) If InStr(1, YS1, ArrYS2(i), vbTextCompare) > 0 Then strAdd2 = "$C$" & i Exit For End If Next i With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=" & strAdd1 & ":" & strAdd2 End With End If End If End If End If End Sub |