|
楼主 |
发表于 2016-11-14 17:21
|
显示全部楼层
好。另外,我改了代码,提示类型不匹配。再看看
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Or Target.Column = 3 Then
Dim d, d1, ar, g
Dim i As Long, k As Long
Dim arr, brr, crr
Dim rng As Range
Set d = CreateObject("Scripting.Dictionary")
Set d1 = CreateObject("Scripting.Dictionary")
Me.UsedRange.Cells.Interior.ColorIndex = 0
ar = Range(Cells(2, 3), Cells(Rows.Count, 3).End(xlUp))
For i = 1 To UBound(ar)
If ar(i, 1) <> "" Then d(ar(i, 1)) = d(ar(i, 1)) + 1
Next
For i = 2 To UBound(ar) + 1
If d(Cells(i, 3).Value) > 1 Then
Cells(i, 3).Interior.ColorIndex = 3
d1(Cells(i, 3).Value) = d1(Cells(i, 3).Value) & " " & Cells(i, 3).Address(0, 0)
Else
ActiveSheet.Pictures.Delete
End If
Next
For Each g In d.keys
If d(g) > 1 Then
n = 0
Cells(1 + n, "F") = "数据:" & g & " 重复次数:" & d(g) & " 单元格:" & d1(g)
n = n + 1
Else
Cells(1 + n, "F") = ""
n = n + 1
End If
Next
crr = [F2].CurrentRegion
With Range("H1").Resize(UBound(crr), 1)
.Value = crr
.Columns.AutoFit
.BorderAround 1, xlThin
.Interior.ColorIndex = 2
.CopyPicture xlScreen, xlPicture
.Clear
Target.Cells(1, .Columns.Count).Select
End With
ActiveSheet.Pictures.Paste
End If
End Sub
|
|