Chart colors for specific items - Excel 2007

Jon Peltier said:

Here's a solution for column or bar charts in Excel 2003. It has a workbook that contains the code and a lookup region, and will work on charts in other workbooks. This approach makes changing colors very easy, because it's done in a worksheet rather than by hardcoding data in the code.

In the lookup workbook, create a sheet named "Lookup". Put your state abbreviations into a range, and color each cell with the desired fill color for that state. Select this range when you're done, and in the Name box type StateLookup and press Enter. This names the range "StateLookup" so the code below can find it easily.

Press Alt+F11 to switch to the VBA Editor. Find this lookup workbook in the project explorer list, right click on it, and choose Insert > Module. Copy this code and paste it into the module:

Code:

Option Explicit
 
Sub ColorPointByStateLookup()
  Dim srs As Series
  Dim vStateNames As Variant
  Dim iPoint As Long
  Dim sStateAbbrev As String
  Dim iColor As Long
  Dim bLookup As Boolean
 
  If Not ActiveChart Is Nothing Then
    Set srs = ActiveChart.SeriesCollection(1)
    vStateNames = srs.XValues
    For iPoint = 1 To srs.Points.Count
      sStateAbbrev = vStateNames(iPoint)
      bLookup = LookupStateColor(sStateAbbrev, iColor)
      If Not bLookup Then
        iColor = xlNone
      End If
      srs.Points(iPoint).Interior.ColorIndex = iColor
    Next
  End If
 
End Sub
 
Function LookupStateColor(sState As String, iColorIndex As Long) As Boolean
  Dim rLookup As Range
  Dim rFound As Range
 
  Set rLookup = ThisWorkbook.Worksheets("Lookup").Range("StateLookup")
  Set rFound = rLookup.Find(sState)
  If rFound Is Nothing Then
    LookupStateColor = False
  Else
    LookupStateColor = True
    iColorIndex = rFound.Interior.ColorIndex
  End If
End Function

The main routine calls the function for each point so it knows what color to use. The function returns True if it finds a match, and the sub uses the returned color index. If the function returns False, the sub removes the fill color of the point. Select a chart on any worksheet and run the sub.

Click to expand...

danpleva said:

Jon:

I was directed to this post by DatSmart and have inserted your code into my workbook, inserted a sheet named Lookup and entered my list of values in a range named StateLookup.

But, when I select an existing chart and run the ColorPointByStateLookup sub, the only thing that happens is my first datapoint turns transparent with a black border and no fill.

I've got more than 80 values to colorize, too, and the drop-down for filling cells doesn't have a "more colors" option...is there a way to assign colors to field values if there are more than the standard colors? Maybe with a separate column in the table listing the hex color code or something?

Thanks!

Click to expand...