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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|