选定的区域内,重复值用不同颜色进行标注
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x(999) As String, y(999, 999) As Long
i = Selection(1).Row
j = Selection(Selection.Count).Row
k = Selection(1).Column
l = Selection(Selection.Count).Column
Range(Cells(1, 1), Cells(999, 99)).Interior.ColorIndex = Null
For a = i To j
For b = k To l
If Cells(a, b) <> "" Then
If g <> "" Then
For c = 1 To g
If Cells(a, b) = x(c) Then
GoTo ss
End If
Next
End If
Z = 0
For c = i To j
For d = k To l
If Cells(a, b) = Cells(c, d) Then
Z = Z + 1
End If
If Z >= 2 Then
GoTo sss
End If
Next
Next
GoTo ss
sss:
g = g + 1
x(g) = Cells(a, b)
For c = i To j
For d = k To l
If x(g) = Cells(c, d) Then
h = h + 1
y(h, 1) = c
y(h, 2) = d
y(h, 3) = g + 2
End If
Next
Next
End If
ss:
Next
Next
For a = 1 To h
Cells(y(a, 1), y(a, 2)).Interior.ColorIndex = y(a, 3)
Next
End Sub
作者 罗绪超 QQ 258016569