Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Or Target.Column = 3 Or Target.Column = 4 Or Target.Column = 5 Then
W = Target.Row
If Len(Cells(W, 2)) * Len(Cells(W, 3)) * Len(Cells(W, 4)) * Len(Cells(W, 5)) <> 0 And Target.Count = 1 Then
Application.EnableEvents = False
ARR = Range("B1:E" & Range("B65536").End(3).Row - 1)
For I = 1 To UBound(ARR)
If ARR(I, 1) = Cells(W, 2) Or ARR(I, 2) = Cells(W, 3) Or ARR(I, 3) = Cells(W, 4) Or ARR(I, 4) = Cells(W, 5) Then
MsgBox "输入数据已经存在!"
Rows(W).Delete
Exit For
End If
Next
Application.EnableEvents = True
End If
End If
End Sub在这个基础上提示与那行或者多行单元格的数据重复,并用颜色填充,也直接选中这些单元格 |