VBA to color data points in a Chart based on non-graphed column values

[shellig]

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.

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
    '[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]
        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 '[B]** I get a run time error '1004': Method 'Cells' of object '_Global' failed[/B]
' 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

 

[johnwm]

Where are these cells:

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:

http://www.essexsteam.co.uk

 

[shellig]

I could probably solve some of the issues by asking the user what column the data is in, what row to start with, and what column to change the colors by.
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:

Sub LoopPoints()
    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
    [I]'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.[/I][/B]
    k = 228  [B][I]'**228 is the first row for the current data plotted.  I need
              to automate this to be the first row for the data plotted[/I][/B]
    Pts = Pts + k - 1
    j = 25 '[I]The standard Excel color cycle for charting: 25-32, 17-24[/I]
    For i = k To Pts '[I]This is the number of datapoints[/I]
  [B]'[I]**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][/B]
  [B]'[I]**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?[/I][/B]
        If Not (Worksheets("Data").Cells(i, 9).Value = Worksheets("Data").Cells(i + 1, 9).Value) Then 'THANKS JOHNWM!
  ' [B][I]**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.[/I][/B]
            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

Thanks for any who have suggestions!
Shelli

 

[shellig]

I haven't gotten any further on this.
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

 

[ab123456]

This code will find the first row in your chart data, its not pretty but it does the trick

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