Hi All,
Thanks for your help in advance.
I have a piece of code which will highlight all duplicates in a row with alternate colours:
Sub colouralternaterows()
Dim Rng As Range, Dn As Range, K As Variant, c As Long
Set Rng = Range(Range("F2"), Range("F" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
If Not .Exists(Dn.Value) Then
.Add Dn.Value, Dn
Else
Set .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
End If
Next
For Each K In .keys
c = c + 1
If c Mod 2 = 0 Then
.Item(K).Interior.Color = RGB(255, 242, 204)
Else
.Item(K).Interior.Color = RGB(217, 217, 217)
End If
Next K
End With
End Sub
I was wondering if anyone knew a way I could set the macro to look within a selection, i.e. I select row A, or A2:A200 manually, and I can then run the code through selecting the module through the quick access toolbar. (Without having to set the range within the code each time.)
The data will be in text form, i.e:
Ben Smith
Ben Smith
John Doe
John Doe
Alice Evans
Alice Evans
Ana Smith
Ana Smith
James Smith
James Smith
James Smith
Daria Burton
Daria Burton
Thanks again for your help,
Alex
Bookmarks