Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #3
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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

  5. #5
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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?

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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
  •