1. I'm looking for a way to color each unique cell entry a different color in a column.

For example, Column A contains a list of 20 cities, but there are not 20 DIFFERENT cities. Let's say there are:

Los Angeles = 1
New York = 2
Honolulu = 3
Minneapolis = 4
New Orleans = 5
Chicago = 5

I need each city to have its own unique color upon execution of a macro. There may be up to 200 different cities listed.

I have a list of colors that can be pulled, or if it assigns it randomnly, that's OK too.

The list won't be in alphabetical or grouped together, but I can handle that aspect of it if that is needed.

Thanks!

2. Excel 2003 and before use a palette of 56 colors, so if you have 200 different cities, you won't be able to give each a unique color. But even if you have more colors at your disposal, as in Excel 2007, users won't be able to distinguish hundreds of colors - for most people, 10 to 20 is the maximum. So you'll have to group cities together so that you'll be able to use a limited number of colors.

3. I'm using 2007. I didn't consider the human limitations. Thanks for pointing that out.

So no that I know they need to be grouped, is there a way to assign various colors? And if there are more in my list, I can just start over with the color assignments.

4. Here's a macro using 10 colors. You can easily expand the number of colors used.

Code:
```Sub ColorCities()
Dim arrColors(1 To 10) As Long
arrColors(1) = RGB(255, 255, 192)
arrColors(2) = RGB(255, 192, 255)
arrColors(3) = RGB(192, 255, 255)
arrColors(4) = RGB(255, 192, 192)
arrColors(5) = RGB(192, 255, 192)
arrColors(6) = RGB(192, 192, 255)
arrColors(7) = RGB(192, 192, 192)
arrColors(8) = RGB(255, 255, 128)
arrColors(9) = RGB(255, 128, 255)
arrColors(10) = RGB(128, 255, 255)
Dim col As New Collection
Dim r As Long
Dim m As Long
Dim i As Integer
Dim strCity As String
Dim lngColor As Long
m = Cells(Rows.Count, 1).End(xlUp).Row
For r = 1 To m
strCity = Cells(r, 1)
On Error Resume Next
lngColor = col(strCity)
If Err Then
i = (i Mod 10) + 1
lngColor = arrColors(i)
End If
On Error GoTo 0
Cells(r, 1).Interior.Color = lngColor
Next r
End Sub```

5. [quote name='HansV' post='778425' date='04-Jun-2009 17:10']Here's a macro using 10 colors. You can easily expand the number of colors used.

<snip>[/quote]

Very nice!

It does work in column A, which is fine. What part of the code "tells" it to work in that column? Is it this part: strCity = Cells(r, 1)

If so, then I could change the 1 to whatever column reference?

Looks like the 1 referenced in my question above refers to a cell offset. I moved my list to Column F and changed it to a 6. It ended up still coloring Column A, but based on the data in Column F.

6. The syntax of Cells is Cells(row number, column number).
The code that I posted has three occurrences of Cells(..., 1). They all refer to column 1, i.e. column A.
If you want it to operate on another column, you must change the number 1 in all three instances of Cells(..., 1).

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•