So I got it pretty much figured out. Let me know if this works for you. (I got the original code from a web site, but I can't for the life of me find it again. I did make some fairly significant changes to that though.)http://usacomp.homeserver.com/Site/TR/Book1.xls
Dim myChart As ChartObject, mysheet As Worksheet
Dim i As Integer, arValues As Variant
For Each ws In Worksheets
For Each myChart In ws.ChartObjects
arValues = .XValues
For i = 1 To UBound(arValues)
.Points(i).Interior.ColorIndex = VLookupStuff(arValues(i))
Function VLookupStuff(ByVal name As String)
VLookupStuff = Application.WorksheetFunction.VLookup(name, Sheets("data").Range("names"), 2, False)
*Have a range in some sheet Have that is a simple 2-column table with the names in one column, and the colorindex
that you would like to use to represent that value in the next column.
*Give this range a name. In this example I have it in a sheet named "data" with the named range of "names". Edit the code to correspond with your choices.
*Now just run the macro (for exmple, by creating a button and assigning it the macro name, or by hitting alt-f8 and choosing it)
A quick explanation of the code:
This uses the vlookup function in excel to lookup the names and return the colors.
As you can see there are nested for loops that basically go through each worksheet in the file, and then selects each chart on that sheet. It then goes and looks at all the points in that chart, looks up what the value is, runs the function that then returns what color you want that to be, and then applies the color. It should be fairly straightforward.
I hope that this helps, and it's open to tweaking and customizing to what you need it to be.