excelvba

Graphic Background and border color change code format


I need your help to change the background and side padding for this graphic code. How can I do this in the example format in the image?

Set EMAChart = Sheets("Parameters").ChartObjects.Add(Left:=Range("a18").Left, Width:=700, Top:=Range("a18").Top, Height:=500)
With EMAChart.Chart
   'EMA_A Grafik
    .Parent.Name = "EMA Chart"

    With .SeriesCollection.NewSeries
        .ChartType = xlLine
        .Values = Sheets("data").Range("e2:e" & numRows)
        .XValues = Sheets("data").Range("a2:a" & numRows)
        .Format.Line.Weight = 1
        .Border.ColorIndex = 1
        .Name = "Price"
    End With

    With .SeriesCollection.NewSeries
        .ChartType = xlLine
        .AxisGroup = xlPrimary
        .Values = Sheets("data").Range("h2:h" & numRows)
        .Name = "EMA_A"
        .Border.ColorIndex = 44
        .Format.Line.Weight = 1

    End With
    'EMA_B Grafik
    With .SeriesCollection.NewSeries
        .ChartType = xlLine
        .AxisGroup = xlPrimary
        .Values = Sheets("data").Range("ı2:ı" & numRows)
        .Name = "EMA_B"
        .Border.ColorIndex = 26
        .Format.Line.Weight = 1

    End With
    'EMA_c Grafik
    With .SeriesCollection.NewSeries
        .ChartType = xlLine
        .AxisGroup = xlPrimary
        .Values = Sheets("data").Range("j2:j" & numRows)
        .Name = "EMA_C"
        .Border.ColorIndex = 17
        .Format.Line.Weight = 1

    End With
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Price"
    .Axes(xlValue, xlPrimary).MaximumScale = WorksheetFunction.Max(Sheets("Data").Range("e2:e" & numRows))
    .Axes(xlValue, xlPrimary).MinimumScale = Int(WorksheetFunction.Min(Sheets("Data").Range("e2:e" & numRows)))
    .Legend.Position = xlLegendPositionBottom
    .SetElement (msoElementChartTitleAboveChart)
    .ChartTitle.Text = " "

End With

enter image description here


Solution

  • The grey area in your example belongs to the 'ChartArea' of the 'Chart' object; you can use Format.Fill to change its color. The black part where the plotting occurs is the PlotArea, it sits inside the ChartArea so transparency of the ChartArea impacts how they look together; example:

        With .ChartArea.Format.Fill
             .ForeColor.RGB = RGB(0, 255, 0)
        End With
        With .PlotArea.Format.Fill
             .ForeColor.RGB = RGB(255, 0, 0)
             .Transparency = 0
        End With