Macro - color coding

[Scottie31]

I have a list of names and the same name may apprear multiple times.

John
Bill
Bill
John
Jack
John
Jack
Bill

The names in the list are different each day. I need to sort this list and then color code each name. The color for John does not have to be green everyday, I just need every instance of John to be one color, Bill another color, etc.. Currently I have to select each name and manually assign a color to it. Some days the list might contain 20 names.

Can this be done using a macro?

 

[Brianwarnock]

If you don't care about the colours then below, modified to your needs should work. This is for column a in sheet2.

Brian

Sub colset1()
Dim lastrow As Long
Dim myrange As Range
Dim cvalue As Integer
cvalue = 3              'first colorindex value

Worksheets("Sheet2").Range("A1").Sort _
        Key1:=Worksheets("Sheet2").Columns("A")                 'sorts the region
lastrow = Sheets("sheet2").Range("A65536").End(xlUp).Row
Worksheets("sheet2").Range("A1").Interior.ColorIndex = cvalue   'sets the colour of the first row
Set myrange = Range(Cells(2, 1), Cells(lastrow, 1))
For Each r In myrange
If r.Value <> r.Offset(-1, 0).Value Then
cvalue = cvalue + 1                                             'increses value when there is a change
End If
r.Interior.ColorIndex = cvalue

Next r

End Sub

 

[Scottie31]

This works perfect....Thanks