Mel Smith
Well-known Member
- Joined
- Dec 13, 2005
- Messages
- 1,041
- Office Version
- 365
- Platform
- Windows
I'm using this code to identify duplicates and what I hoped for was that the cell of any duplicates would be highlighted but that isn't happening. All I get is a screen message saying "Ambiguous name detected. Compare data."
My code is:
Sub CompareData()
Application.ScreenUpdating = False
Dim srcWS As Worksheet, desWS As Worksheet, v1 As Variant, v2 As Variant, dic As Object
Set srcWS = Sheets("First Class")
Set desWS = Sheets("T20")
v1 = srcWS.Range("B2", srcWS.Range("B" & Rows.Count).End(xlUp)).Value
v2 = desWS.Range("B2", desWS.Range("B" & Rows.Count).End(xlUp)).Value
Set dic = CreateObject("Scripting.Dictionary")
For i = LBound(v2) To UBound(v2)
If Not dic.exists(v2(i, 1)) Then
dic.Add v2(i, 1), i + 1
End If
Next i
For i = LBound(v1) To UBound(v1)
If dic.exists(v1(i, 1)) Then
desWS.Range("B" & dic(v1(i, 1))).Interior.ColorIndex = 24
End If
Next i
Application.ScreenUpdating = True
End Sub
Many thanks. Mel
P.S. I couldn't use the button to post my code.
My code is:
Sub CompareData()
Application.ScreenUpdating = False
Dim srcWS As Worksheet, desWS As Worksheet, v1 As Variant, v2 As Variant, dic As Object
Set srcWS = Sheets("First Class")
Set desWS = Sheets("T20")
v1 = srcWS.Range("B2", srcWS.Range("B" & Rows.Count).End(xlUp)).Value
v2 = desWS.Range("B2", desWS.Range("B" & Rows.Count).End(xlUp)).Value
Set dic = CreateObject("Scripting.Dictionary")
For i = LBound(v2) To UBound(v2)
If Not dic.exists(v2(i, 1)) Then
dic.Add v2(i, 1), i + 1
End If
Next i
For i = LBound(v1) To UBound(v1)
If dic.exists(v1(i, 1)) Then
desWS.Range("B" & dic(v1(i, 1))).Interior.ColorIndex = 24
End If
Next i
Application.ScreenUpdating = True
End Sub
Many thanks. Mel
P.S. I couldn't use the button to post my code.