Results 1 to 2 of 2
  1. #1

    Chart color formatting

    Is there a way in Excel 97 to have the format of the chart data series be the color format of the cell? e.g., If I have my data series font as red, the line in my chart would be red.

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Kortrijk, Belgium
    Thanked 0 Times in 0 Posts

    Re: Chart color formatting

    This is not straightforward but can be done with a macro. However, it is very difficult and will surely take time to write code in a general way as to be applicable to many different chart types, taking into account the presence of multiple series in a chart etc. Moreover, the color of your datapoints can even be different from cell to cell, so in the code below I made several assumptions.
    1) as an example, I took an XY scatterplot
    2) there was only 1 series on the chart
    3) the chart was embedded in a spreadsheet and it was the only chart in the spreadsheet
    4) the colorindex of the font of the first cell of the VALUES (that is, the Y-values) is used to set the color of the markers in the chart
    5) I here only plotted markers, no lines, but you can adjust the code as you wish (just record a macro, add a line and see what code is generated)

    The main difficulty here lies in the fact that there is no way to obtain the range address of the Values or the xlValues from the seriesformula directly via a command in VBA. When you try to read the values or xlvalues, Excel always returns an array, not a range address. That's why the GetChartRange procedure is needed. The rest of the code is straightforward: the chart on the spreadsheet is defined in MyChart; the series in the chart is defined in Sseries. From the GetChartRange procedure, the range address of the 'Values' is obtained. Then the colorindex of the font of the first cell in that range is read and the same color is applied to the markerbackgroundcolorindex and markerforegroundcolorindex. I also encountered some problems with the listseparator. Although, the listseparator in my Excel version (defined in the Windows regional settings) is a semi-colon, I had to apply the comma in my VBA code. Don't understand why, but it worked only then. Hope this points you to a solution for your particular case.

    Sub ChangeSeriesColorToDataColor()
    Dim Sseries As Series
    Dim Serie As Integer
    Dim SeriesColor As Integer
    Dim MyChart As Chart
    Dim R As Range
    Serie = 1
    Set MyChart = ActiveSheet.ChartObjects(1).Chart
    Set Sseries = MyChart.SeriesCollection(Serie)
    Set R = GetChartRange(MyChart, 1, "Values")
    SeriesColor = R.Cells(1, 1).Font.ColorIndex
    With Sseries.Border
    .Weight = xlHairline
    .LineStyle = xlNone
    End With
    With Sseries
    .MarkerBackgroundColorIndex = SeriesColor
    .MarkerForegroundColorIndex = SeriesColor
    End With
    End Sub

    Private Function GetChartRange(Ch As Chart, Ser As Integer, ValXorY As String) As Range
    Dim SeriesFormula As String
    Dim ListSep As String * 1
    Dim Pos As Integer
    Dim LSeps() As Integer
    Dim Txt As String
    Dim i As Integer
    Set GetChartRange = Nothing
    On Error Resume Next
    SeriesFormula = Ch.SeriesCollection(Ser).Formula
    ListSep = ","
    For i = 1 To Len(SeriesFormula)
    If Mid$(SeriesFormula, i, 1) = ListSep Then
    Pos = Pos + 1
    ReDim Preserve LSeps(Pos)
    LSeps(Pos) = i
    End If
    Next i
    If UCase(ValXorY) = "XVALUES" Then
    Txt = Mid$(SeriesFormula, LSeps(1) + 1, LSeps(2) - LSeps(1) - 1)
    Set GetChartRange = Range(Txt)
    End If
    If UCase(ValXorY) = "VALUES" Then
    Txt = Mid$(SeriesFormula, LSeps(2) + 1, LSeps(3) - LSeps(2) - 1)
    Set GetChartRange = Range(Txt)
    End If
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts