VBA for the Fill color is Red and same border color, line color is no in Chart

[kpmsivaprakasam2003]

Hi, I am using the Excel 2013

I have done through recording macro. i have not to change the Fill color is Red and same border color, line color is no.

kindly provide the vba code and thanks for advance

Table Data

	target	actul	bad	good	ver good
a	250​	245​	150​	220​	250​
b	350​	300​	280​	330​	350​
c	280​	250​	250​	265​	280​
d	180​	190​	160​	165​	180​

VBA Macro

Sub Multiple_Bullets_Chart()
'
' Macro12 Macro
'

'
    Range("D1:F5").Select
    ActiveSheet.Shapes.AddChart2(216, xlBarClustered).Select
    ActiveChart.SetSourceData Source:=Range("Sheet3!$D$1:$F$5")
   
    ActiveChart.FullSeriesCollection(3).Select
    ActiveChart.ChartGroups(1).Overlap = 100
    ActiveChart.ChartGroups(1).GapWidth = 50
   
    ActiveChart.FullSeriesCollection(1).XValues = "=Sheet3!$A$2:$A$5"
   
    ActiveChart.FullSeriesCollection(1).PlotOrder = 3
    ActiveChart.FullSeriesCollection(1).PlotOrder = 2
   
    ActiveChart.FullSeriesCollection(3).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = -0.25
        .Transparency = 0
        .Solid
    End With
    ActiveChart.FullSeriesCollection(2).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0.400000006
        .Transparency = 0
        .Solid
    End With
    ActiveChart.FullSeriesCollection(1).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0.8000000119
        .Transparency = 0
        .Solid
    End With
   
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(4).Name = "=Sheet3!$B$1"
    ActiveChart.FullSeriesCollection(4).Values = "=Sheet3!$B$2:$B$5"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(5).Name = "=Sheet3!$C$1"
    ActiveChart.FullSeriesCollection(5).Values = "=Sheet3!$C$2:$C$5"
 
    ActiveChart.FullSeriesCollection(5).Select
    ActiveChart.ChartArea.Select
    ActiveChart.FullSeriesCollection(5).ChartType = xlXYScatter
    ActiveChart.FullSeriesCollection(4).ChartType = xlXYScatter
    ActiveChart.FullSeriesCollection(4).Select
    ActiveChart.FullSeriesCollection(4).XValues = "=Sheet3!$B$2:$B$5"
    ActiveChart.FullSeriesCollection(4).Values = "={10,30,50,70}"
    ActiveChart.FullSeriesCollection(5).XValues = "=Sheet3!$C$2:$C$5"
    ActiveChart.FullSeriesCollection(5).Values = "={10,30,50,70}"
 
 
    ActiveChart.FullSeriesCollection(4).Select
    With Selection
        .MarkerStyle = 8
        .MarkerSize = 5
    End With
    Selection.MarkerSize = 6
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(192, 0, 0)
        .BackColor.RGB = RGB(192, 0, 0)
        .Transparency = 0
      
    End With
    Selection.Format.Line.Visible = msoFalse
   
    ActiveChart.FullSeriesCollection(5).Select
    With Selection
        .MarkerStyle = 8
        .MarkerSize = 5
    End With
    Selection.MarkerSize = 6
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 0, 0)
        .BackColor.RGB = RGB(0, 0, 0)
        .Transparency = 0
      
    End With
   
    Selection.Format.Line.Visible = msoFalse
   

End Sub

 

[sijpie]

In your code there is a section on the formatting of the series 4, which is your red bullet.

Replace it with the following:

 ActiveChart.FullSeriesCollection(4).Select
    With Selection
        .MarkerStyle = 8
        .MarkerSize = 5
    End With
    Selection.MarkerSize = 6
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(192, 0, 0)
        .BackColor.RGB = RGB(192, 0, 0)
        .Transparency = 0
      
    End With
    With Selection.Format.Line
        .ForeColor.RGB = RGB(192, 0, 0)
        .Visible = msoTrue
    End With
    ' Selection.Format.Line.Visible = msoFalse 'this sets the outline to be not visible