A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Retaining Data Point Colors while Deleting a previously plotted Column?



 
 
Thread Tools Display Modes
  #1  
Old October 10th, 2003, 08:19 PM
Ken Black
external usenet poster
 
Posts: n/a
Default Retaining Data Point Colors while Deleting a previously plotted Column?

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
Ads
  #2  
Old October 11th, 2003, 03:24 AM
Jon Peltier
external usenet poster
 
Posts: n/a
Default Retaining Data Point Colors while Deleting a previously plottedColumn?

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


  #3  
Old October 15th, 2003, 10:11 PM
Ken Black
external usenet poster
 
Posts: n/a
Default Retaining Data Point Colors while Deleting a previously plotted Column?

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




Jon Peltier wrote in message ...
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

  #4  
Old October 16th, 2003, 06:05 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default Retaining Data Point Colors while Deleting a previously plottedColumn?

Ken -

So the color of the point is based upon the value? This is easier to
do. You cannot apply conditional formatting to a series the way you can
to a worksheet range, but you can make a chart that shows different
formatting depending upon anything you can put into a cell (like a value
or a label). See the Conditional Formatting examples on my web site:

http://www.geocities.com/jonpeltier/...html#CondChart

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

Ken Black wrote:
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




Jon Peltier wrote in message ...

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


  #5  
Old October 17th, 2003, 03:54 PM
Ken Black
external usenet poster
 
Posts: n/a
Default Retaining Data Point Colors while Deleting a previously plotted Column?

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

Jon Peltier wrote in message ...
Ken -

So the color of the point is based upon the value? This is easier to
do. You cannot apply conditional formatting to a series the way you can
to a worksheet range, but you can make a chart that shows different
formatting depending upon anything you can put into a cell (like a value
or a label). See the Conditional Formatting examples on my web site:

http://www.geocities.com/jonpeltier/...html#CondChart

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

Ken Black wrote:
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




Jon Peltier wrote in message ...

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:55 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 OfficeFrustration.
The comments are property of their posters.