[RESOLVED] How to change color of Excel chart point?

[Mark@SF]

I'm having trouble changing the color of a point in an Excel line chart.

The code snippet below loops through a range of dates checking each one to see if it is either the minimum or maximum date. If it is the minimum date, then the color of the point is set to red. If it is the maximum date, then the color of the point is set to green.

The intPointIndex allows the code to match the series points with the corresponding rows of data (dates).

srs = CType(myChart.SeriesCollection(1), Excel.Series)

For Each c In xlsWS.Range(xlsWS.Range("A1:A1"), xlsWS.Range("A1:A1").End(Excel.XlDirection.xlDown)).Cells
    intPointIndex += 1
    If IsDate(CType(c, Excel.Range).Value) Then
        Debug.WriteLine(CType(c, Excel.Range).Value)
        If CDate(CType(c, Excel.Range).Value).ToShortDateString = dtmMinDate.ToShortDateString Then
            pt = CType(srs.Points(intPointIndex - 1), Excel.Point)
            pt.MarkerBackgroundColor = RGB(255, 0, 0)    '...red (3)
            pt.MarkerForegroundColor = RGB(255, 0, 0)    '...red (3)
            pt.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle
        ElseIf CDate(CType(c, Excel.Range).Value).ToShortDateString = dtmMaxDate.ToShortDateString Then
            pt = CType(srs.Points(intPointIndex - 1), Excel.Point)
            pt.MarkerBackgroundColor = RGB(0, 255, 0)    '...green (4)
            pt.MarkerForegroundColor = RGB(0, 255, 0)    '...green (4)
            pt.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle
        End If
    End If
Next c

My coded compiles w/o error, but the point colors and markers don't change.

What am I doing wrong?

[bmwpete]

I do not use Excel very much but you seem to be assigning to the point variable pt, set the properties of that but never actually change the original data i.e. whatever is in srs, would you not set srs.Points(intPointIndex - 1) = pt?

[Delaney]

Hello,

This code is doing what you want (change color of point for a given date) so, maybe, it can give to you some ideas

      Dim xl As New Excel.Application
        Dim wb As Excel.Workbook
        Dim critere As Date
        Dim origine As Date = #1/1/1900#


        wb = xl.Workbooks.Open("path\XXXX.xlsm")

        Dim chart1 = wb.Worksheets("Feuil1").ChartObjects(1).Chart
        Dim Ser = chart1.SeriesCollection(1)
        Dim vals = Ser.values

        critere = wb.Worksheets("Feuil1").range("H2").value
        Dim date_nb As Integer = critere.Subtract(origine).Days + 2 '  because it take the number of day between the 2 dates


        For i = LBound(vals) To UBound(vals)
            If vals(i) = CDbl(date_nb) Then
                Ser.points(i).MarkerBackgroundColor = RGB(0, 255, 0)    ' green
                Ser.points(i).MarkerForegroundColor = RGB(0, 255, 0)
            End If
        Next

        wb.Close(True)
        xl.Quit()

[Mark@SF]

My problem was that I referred to “myChart” when instantiating the Series object. After fixing that, the code works fine. I’m going to mark this thread as resolved.