Ken -

As long as the points are in the same series, Excel remembers the colors
by point index, not by specific category or X value. The first point
always has the first color, etc.

But you can write a macro that recognizes what month it is, and will
always use the same color for January, another color for February, etc,
no matter if it's the first or twelfth month of the series:

' -------------------------------------------------------------------
Sub ColorPointsByMonth(oChart As Chart)
Dim iPtIx As Integer
Dim iPtCt As Integer
Dim vMonths As Variant
With ActiveChart.SeriesCollection(1)
vMonths = .XValues
iPtCt = .Points.Count
For iPtIx = 1 To iPtCt
Select Case Month(vMonths(iPtIx))
Case 1, 7 ' Jan, Jul
ColorPoint .Points(iPtIx), 3 ' Red
Case 2, 8 ' Feb, Aug
ColorPoint .Points(iPtIx), 46 ' Orange
Case 3, 9
ColorPoint .Points(iPtIx), 6 ' Yellow
Case 4, 10
ColorPoint .Points(iPtIx), 4 ' Green
Case 5, 11
ColorPoint .Points(iPtIx), 5 ' Blue
Case 6, 12
ColorPoint .Points(iPtIx), 13 ' Purple
End Select
Next
End With
End Sub

Sub ColorPoint(oPoint As Point, iColor As Integer)
oPoint.MarkerBackgroundColorIndex = iColor
oPoint.MarkerForegroundColorIndex = iColor
End Sub
' -------------------------------------------------------------------

When you update the data range of the charts, run one of the following,
that in turn runs the macro above.

' -------------------------------------------------------------------
Sub ColorActiveChart()
ColorPointsByMonth ActiveChart
End Sub

Sub ColorChartSheets()
Dim oChart As Chart
For Each oChart In ActiveWorkbook.Charts
ColorPointsByMonth oChart
Next
End Sub

Sub ColorChartsOnActiveSheet()
Dim oChtob As ChartObject
For Each oChtob In ActiveSheet.ChartObjects
ColorPointsByMonth oChtob.Chart
Next
End Sub

Sub ColorChartsOnAllSheets()
Dim oSheet As Object
Dim oChtob As ChartObject
For Each oSheet In ActiveWorkbook.Sheets
For Each oChtob In oSheet.ChartObjects
ColorPointsByMonth oChtob.Chart
Next
Next
End Sub

Sub ColorEveryLastChart()
Dim oSheet As Object
Dim oChtob As ChartObject
Dim oChart As Chart
For Each oSheet In ActiveWorkbook.Sheets
For Each oChtob In oSheet.ChartObjects
ColorPointsByMonth oChtob.Chart
Next
Next
For Each oChart In ActiveWorkbook.Charts
ColorPointsByMonth oChart
Next
End Sub
' -------------------------------------------------------------------


- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______


Ken Black wrote:
I cannot determine how to keep the data point color associated with a
particular cell in a series from moving. The problem has only occured
as I began going past 12 months of data, under a 12 month rolling data
format. This is the first month that we have more than 12 months, so I
delete the Sept 02 column and add a Sept 03 column.

For some reason, Excel shifts all the previously defined data point
colors to the right by one data point (actually, the data point values
seem to move to the left one position (desired), while the colors stay
in place (undesired). If I could use absolute references in the data
series, I may be able to retain the color. But Excel doesn't allow
that.

I tried keeping the Sep 02 data in the series, but empty, and this
retains the color/data point positions, but leaves a gap at the
beginning of the chart.

Also, this is a large report with 50 charts, and there are multiple
similar reports done monthly. So, all told about 250 charts. Using
the Conditional Formatting for Charts trick would be too much of a
task for me to set up on so many charts.

Does anyone know a fix or work around/trick for this?

Thank you,

Ken Black