As per title, please can you explain this code? I found it on the internet and "although it does what I want" I do not understand how it works and cannot modify it.
code []
Sub HighlightDupes()
' this works if column is filtered and only
'on visible rows
Dim i As Long, dic As Variant, v As Variant
Dim wkbnme As String ' name of workbook to check
For Each wkb In Workbooks ' make sure only the workbook you want to check is visible
If Windows(wkb.Name).Visible Then
If wkbnme = "" Then
wkbnme = wkb.Name
End If
End If
Next
Windows(wkbnme).Activate 'workbook to check
Columns("A:A").Select ' the column to check
Application.ScreenUpdating = False ' this does not seem to do anything?
Set dic = CreateObject("Scripting.Dictionary")
i = 1
For Each v In Selection.Value2
If dic.exists(v) Then dic(v) = "" Else dic.Add v, i
i = i + 1
Next v
Selection.Font.Color = 255 'changes column text to red
For Each v In dic
If dic(v) <> "" Then Selection(dic(v)).Font.Color = 0 ' changes font of duplicates to black
Next v
If counter > 0 Then
MsgBox ("duplicates found")
ElseIf counter = 0 Then
MsgBox ("No duplictes found")
End If
End Sub
[/code]
Thee are the bits I do not understand
Set dic = CreateObject("Scripting.Dictionary")
For Each v In Selection.Value2
If dic.exists(v) Then dic(v) = "" Else dic.Add v, i
i = i + 1
Selection.Font.Color = 255 'changes column text to red
For Each v In dic
If dic(v) <> "" Then Selection(dic(v)).Font.Color = 0 ' changes font of duplicates to black
Next v
I would also like to count the number of duplicates but cant work out how to count the duplicates.
thank you
smiler44
code []
Sub HighlightDupes()
' this works if column is filtered and only
'on visible rows
Dim i As Long, dic As Variant, v As Variant
Dim wkbnme As String ' name of workbook to check
For Each wkb In Workbooks ' make sure only the workbook you want to check is visible
If Windows(wkb.Name).Visible Then
If wkbnme = "" Then
wkbnme = wkb.Name
End If
End If
Next
Windows(wkbnme).Activate 'workbook to check
Columns("A:A").Select ' the column to check
Application.ScreenUpdating = False ' this does not seem to do anything?
Set dic = CreateObject("Scripting.Dictionary")
i = 1
For Each v In Selection.Value2
If dic.exists(v) Then dic(v) = "" Else dic.Add v, i
i = i + 1
Next v
Selection.Font.Color = 255 'changes column text to red
For Each v In dic
If dic(v) <> "" Then Selection(dic(v)).Font.Color = 0 ' changes font of duplicates to black
Next v
If counter > 0 Then
MsgBox ("duplicates found")
ElseIf counter = 0 Then
MsgBox ("No duplictes found")
End If
End Sub
[/code]
Thee are the bits I do not understand
Set dic = CreateObject("Scripting.Dictionary")
For Each v In Selection.Value2
If dic.exists(v) Then dic(v) = "" Else dic.Add v, i
i = i + 1
Selection.Font.Color = 255 'changes column text to red
For Each v In dic
If dic(v) <> "" Then Selection(dic(v)).Font.Color = 0 ' changes font of duplicates to black
Next v
I would also like to count the number of duplicates but cant work out how to count the duplicates.
thank you
smiler44