Retaining Data Point Colors while Deleting a previously plotted Column?

  • Thread starter Thread starter Ken Black
  • Start date Start date
K

Ken Black

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
 
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,

Thanks for the feedback. I don't think the macro will help in my
situation. Each chart may have different colored data points assigned
to different months (the point colors represent the level of
performance for a particular month using red, yellow, and green), so
I'd probably need a different macro for each of the 250 charts, which
is not feasible.

As you mentioned the way Excel remembers the colors by point index, I
wonder if there is some way of tricking Excel into thinking point 2 is
now point 1, point 3 is now point 2, etc. (subtract one from the index
count). If a macro could do this, then possibly one macro could be
used for each chart.

Any ideas on this approach?

Thank you,
Ken
 
I will have to reconsider that approach. I had played around with the
conditional formatting for charting example from you site, but it
would be quite a task to set up initially, given there are so many
charts (and the way they are currently structured). But maybe I can
come up with a way to implement it nicely.

Thanks for the help Jon!

Ken
 
Back
Top