Assign Different Colors

Topic

New Reply

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!

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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)
	  col.Add Item:=lngColor, Key:=strCity
	End If
	On Error GoTo 0
	Cells(r, 1).Interior.Color = lngColor
  Next r
End Sub

Reply | Quote

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

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?

Thanks again for your help.

Edited to add:

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.

Reply | Quote

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).

Reply | Quote