本帖最后由 安全网 于 2016-1-8 16:59 编辑
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 2 And Target.Column < 9 Then
W = Target.Row
If Len(Cells(W, 3)) * Len(Cells(W, 4)) * Len(Cells(W, 5)) * Len(Cells(W, 8)) <> 0 And Target.Count = 1 Then
Application.EnableEvents = False
ARR = Range("C1:H" & Range("C65536").End(3).Row - 1)
For I = 1 To UBound(ARR)
If ARR(I, 1) = Cells(W, 3) And ARR(I, 2) = Cells(W, 4) And ARR(I, 3) = Cells(W, 5) And ARR(I, 6) = Cells(W, 8) Then
Range("C" & I & ":H" & I).Interior.ColorIndex = 6
If MsgBox("输入数据与第 " & I & " 行相同!是否需要删除?", 4 + 32 + 256) = 6 Then
Rows(W).Delete
Range("C2:H" & I).Interior.ColorIndex = 0
End If
'Exit For
End If
Next
Application.EnableEvents = True
End If
End If
End Sub这个代码提示了输入重复数据时提示,但要修改已经输入的数据时,就会提示与本行数据重复
本帖最后由 zjdh 于 2016-1-8 16:53 编辑
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 2 And Target.Column < 9 Then
W = Target.Row
If Len(Cells(W, 3)) * Len(Cells(W, 4)) * Len(Cells(W, 5)) * Len(Cells(W, 8)) <> 0 And Target.Count = 1 Then
Application.EnableEvents = False
Arr = Range("C1:H" & Range("C65536").End(3).Row )
For I = 1 To UBound(Arr)
If Arr(I, 1) = Cells(W, 3) And Arr(I, 2) = Cells(W, 4) And Arr(I, 3) = Cells(W, 5) And Arr(I, 6) = Cells(W, 8) Then
If Target.Row = I Then GoTo 10
Range("C" & I & ":H" & I).Interior.ColorIndex = 6
If MsgBox("输入数据与第 " & I & " 行相同!是否需要删除?", 4 + 32 + 256) = 6 Then
Rows(W).Delete
Range("C2:H" & I).Interior.ColorIndex = 0
End If
'Exit For
End If
Next
End If
End If
10 Application.EnableEvents = True
End Sub
|