VBA code for format fill and border line color for Scatter Chart in Excel 2013

[sivaprakasam]

Hi i am using the excel 2013

VBA code for format fill and borderline color for Scatter Chart in Excel 2013

I tried the recording macro the “Clustered Bar Chart” and “Scatter Chart”. but I cannot make the recording macro the format fill and border line color

I expect below the chart

1. For series "target" - the Marker Fill color is Red and the same border color, line color is no.
2. For series "actul" - the Marker Fill color is Black and the same border color, line color is no.

Kindly provide the VBA code and Thanks for Advance

 

[p45cal]

Try this for the entire macro:

Sub Multiple_Bullets_Chart()
Range("D1:F5").Select
ActiveSheet.Shapes.AddChart2(216, xlBarClustered).Select
With ActiveChart
  .SetSourceData Source:=Range("Sheet3!$D$1:$F$5")
  .ChartGroups(1).Overlap = 100
  .ChartGroups(1).GapWidth = 50
   
  .FullSeriesCollection(1).XValues = "=Sheet3!$A$2:$A$5"
  .FullSeriesCollection(1).PlotOrder = 3
  .FullSeriesCollection(1).PlotOrder = 2

  Brightnss = Array(0.8000000119, 0.400000006, -0.25)
  OptBase = 1 - LBound(Brightnss)
  For i = 1 To 3
    With .FullSeriesCollection(i).Format.Fill
      .Visible = msoTrue
      .ForeColor.ObjectThemeColor = msoThemeColorAccent1
      .ForeColor.TintAndShade = 0
      .ForeColor.Brightness = Brightnss(i - OptBase)    '0.8000000119
      .Transparency = 0
      .Solid
    End With
  Next i
   
  With .SeriesCollection.NewSeries
    .Name = "=Sheet3!$B$1"
    .Values = "=Sheet3!$B$2:$B$5"
    .ChartType = xlXYScatter
    .XValues = "=Sheet3!$B$2:$B$5"
    .Values = "={10,30,50,70}"
    .MarkerStyle = 8
    .MarkerSize = 6
    .MarkerBackgroundColor = 192
    .MarkerForegroundColor = 192
  End With
   
  With .SeriesCollection.NewSeries
    .Name = "=Sheet3!$C$1"
    .Values = "=Sheet3!$C$2:$C$5"
    .ChartType = xlXYScatter
    .XValues = "=Sheet3!$C$2:$C$5"
    .Values = "={10,30,50,70}"
    .MarkerStyle = 8
    .MarkerSize = 6
    .MarkerBackgroundColor = 0
    .MarkerForegroundColor = 0
  End With
End With
End Sub

The problem was the missing .MarkerBack/ForegroundColor = lines which the vba recorder didn't record properly.

 

[sivaprakasam]

Try this for the entire macro:

Sub Multiple_Bullets_Chart()
Range("D1:F5").Select
ActiveSheet.Shapes.AddChart2(216, xlBarClustered).Select
With ActiveChart
  .SetSourceData Source:=Range("Sheet3!$D$1:$F$5")
  .ChartGroups(1).Overlap = 100
  .ChartGroups(1).GapWidth = 50
  
  .FullSeriesCollection(1).XValues = "=Sheet3!$A$2:$A$5"
  .FullSeriesCollection(1).PlotOrder = 3
  .FullSeriesCollection(1).PlotOrder = 2

  Brightnss = Array(0.8000000119, 0.400000006, -0.25)
  OptBase = 1 - LBound(Brightnss)
  For i = 1 To 3
    With .FullSeriesCollection(i).Format.Fill
      .Visible = msoTrue
      .ForeColor.ObjectThemeColor = msoThemeColorAccent1
      .ForeColor.TintAndShade = 0
      .ForeColor.Brightness = Brightnss(i - OptBase)    '0.8000000119
      .Transparency = 0
      .Solid
    End With
  Next i
  
  With .SeriesCollection.NewSeries
    .Name = "=Sheet3!$B$1"
    .Values = "=Sheet3!$B$2:$B$5"
    .ChartType = xlXYScatter
    .XValues = "=Sheet3!$B$2:$B$5"
    .Values = "={10,30,50,70}"
    .MarkerStyle = 8
    .MarkerSize = 6
    .MarkerBackgroundColor = 192
    .MarkerForegroundColor = 192
  End With
  
  With .SeriesCollection.NewSeries
    .Name = "=Sheet3!$C$1"
    .Values = "=Sheet3!$C$2:$C$5"
    .ChartType = xlXYScatter
    .XValues = "=Sheet3!$C$2:$C$5"
    .Values = "={10,30,50,70}"
    .MarkerStyle = 8
    .MarkerSize = 6
    .MarkerBackgroundColor = 0
    .MarkerForegroundColor = 0
  End With
End With
End Sub

The problem was the missing .MarkerBack/ForegroundColor = lines which the vba recorder didn't record properly.

Tnq so much.... It works perfectly and thanks for the new code.......