VBA to color data points in a Chart based on non-graphed column values
VBA to color data points in a Chart based on non-graphed column values
(OP)
Instead of data labels, I sometimes go in and manually change data point colors based on values in another column (like yield values that are graphed and color changes based on input material lot number). What is annoying about this is if the data range of the graph is changed, the colors that I put in manually are no longer correct.
I am trying to figure out a VBA way to color the data points
Here is my first shot.
I am stuck though. See the ** comments in the code.
I am trying to figure out a VBA way to color the data points
Here is my first shot.
I am stuck though. See the ** comments in the code.
CODE
Sub LoopPoints()
Dim Cht As Chart
Dim i As Integer
Dim j As Integer
Set Cht = ActiveChart
' Set Cht = ActiveSheet.ChartObjects(1).Chart 'If embedded
Pts = Cht.SeriesCollection(1).Points.Count 'Looks at only the first series which is all I need for my application
For i = 1 To Pts
'**What if the first point is not the first row? "i" should be the value of the first row of charted data and Pts = total number of points plus value of first row. I am not sure how to find what the value of the first row of charted data is.
j = 25 'This is the standard color cycle for charting in excel 25-32, 17-24
If Not (Cells(i, 8).Value = Cells(i + 1, 8).Value) Then '** I get a run time error '1004': Method 'Cells' of object '_Global' failed
' Data that determines data point color change is in column 8 in this worksheet
j = j + 1
If j = 33 Then
j = 17 'J Cycle from 17 to 32 (standard)
End If
Cht.SeriesCollection(1).Points(i).MarkerBackgroundColorIndex = j
End If
Next i
End Sub
Dim Cht As Chart
Dim i As Integer
Dim j As Integer
Set Cht = ActiveChart
' Set Cht = ActiveSheet.ChartObjects(1).Chart 'If embedded
Pts = Cht.SeriesCollection(1).Points.Count 'Looks at only the first series which is all I need for my application
For i = 1 To Pts
'**What if the first point is not the first row? "i" should be the value of the first row of charted data and Pts = total number of points plus value of first row. I am not sure how to find what the value of the first row of charted data is.
j = 25 'This is the standard color cycle for charting in excel 25-32, 17-24
If Not (Cells(i, 8).Value = Cells(i + 1, 8).Value) Then '** I get a run time error '1004': Method 'Cells' of object '_Global' failed
' Data that determines data point color change is in column 8 in this worksheet
j = j + 1
If j = 33 Then
j = 17 'J Cycle from 17 to 32 (standard)
End If
Cht.SeriesCollection(1).Points(i).MarkerBackgroundColorIndex = j
End If
Next i
End Sub





RE: VBA to color data points in a Chart based on non-graphed column values
If Not (Cells(i, 8).Value = Cells(i + 1, 8).Value)
The Cells property belongs to another object, which you have not defined. Therefore Excel tries to find the Cell property of the '_Global' object (which doesn't have a Cells property). If in the currently active sheet you can refer by using:
ActiveSheet.Cells
or by using:
Sheets("mySheet").Cells
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
UK steam enthusiasts: www.essexsteam.co.uk
RE: VBA to color data points in a Chart based on non-graphed column values
I haven't done that because I would like to take the chart as plotted and have it automatically update the colors, even if the range is changed.
I have not seen anywhere where I can pull out the range column or row to input as variables in a loop. I am sure this can be done but am clueless how!!
Updated code:
CODE
Dim Cht As Chart
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim m As Integer
Set Cht = ActiveChart
' Set Cht = ActiveSheet.ChartObjects(1).Chart 'If embedded
Pts = Cht.SeriesCollection(1).Points.Count
'Looks at only the first series which is all I need for my application
'[B]**What if the first point is not the first row? "i" should be
the value of the first row of charted data and Pts = total number of
points plus value of first row. I am not sure how to find what the
value of the first row of charted data is.[/B]
k = 228 '**228 is the first row for the current data plotted. I need
to automate this to be the first row for the data plotted
Pts = Pts + k - 1
j = 25 'The standard Excel color cycle for charting: 25-32, 17-24
For i = k To Pts 'This is the number of datapoints
'**Another problem. The cell may be blank and the data point not plotted.
Need to skip that cell and go to the next. Also need to determine which
column the data being plotted is in (as before I needed the first row of
the data being plotted).
'**I could try If IsEmpty(Worksheets("Data").Cells(i, <COLUMN WITH DATA>))
Then can I say i=i+1 and Pts=Pts +1? Or should I try to count the blanks
first and add to the loop?
If Not (Worksheets("Data").Cells(i, 9).Value = Worksheets("Data").Cells(i + 1, 9).Value) Then 'THANKS JOHNWM!
' **Data that determines data point color change is in column 9 in this
worksheet maybe I should automate this so the user can specify the
column.
j = j + 1
If j = 33 Then
j = 17 'J Cycle from 17 to 32 (standard)
End If
End If
Cht.SeriesCollection(1).Points(i).MarkerBackgroundColorIndex = j
Next i
End Sub
Shelli
RE: VBA to color data points in a Chart based on non-graphed column values
I am having the biggest difficulty figuring out how to get column and row information from charted data
In a chart, series are specified like:
=SERIES[ (series_name) , (X-values) , (Y-values) , (plot_order) ].
Range references in a SERIES formula are always absolute and always contain a worksheet name. If you have defined named ranges then the series formula can contain a named range in place of a range.
=SERIES('g2'!$G$36,'g2'!$A$37:$A$71,'g2'!$G$37:$G$ 71,7)
If I want to write VBA code that knows that my data starts on row 37 or that the Y values are in column G (numerically=7), how do I pull that information?
Is there a way?
http://www.j-walk.com/ss/excel/tips/tip83.htm
is probably a good starting point but honestly I am having a terrible time deciphering it as I am a sporadic VBA user.
Thanks!
Shelli
RE: VBA to color data points in a Chart based on non-graphed column values
chartrange = Cht.SeriesCollection(1).Formula
splitrange = split(chartrange,"$")
firstrow = Left(splitrange(2), Len(splitrange(2)) - 1)
for i = firstrow to firstrow + pts
The chartrange variable reads the data range from your chart and will take the value of something like '=SERIES(sheet1!$a$1:$a12,sheet2!$b$1:$b$12) the other two rows of the code then extract the row number. The split command breaks the chartrange into a number of chunks each separated by a $ and stores each chunk in the splitrange array. The chunk of data we need is between the 2nd and 3rd $ signs. The final row removes the : and allows you to use it in your for .. next loop.
There is probably a neater way to do this but this should work