My code referring to the colour of the trendline has stopped working.
It was previously working fine, but now threndline is white.
It was previously working fine, but now threndline is white.
Rich (BB code):
Sub AddDeals()
'
' AddDeals Macro
Dim Wkb As Excel.Workbook
Dim ws As Sheets
Set Wkb = ThisWorkbook
Set ws = ThisWorkbook.Worksheets
ws_count = Wkb.Worksheets.Count
'Begin loop
For i = 6 To ws_count
Wkb.Sheets(i).Activate
ActiveSheet.Range("Q3:s12").Select
ws(i).Shapes.AddChart(xlColumnClustered, _
Left:=ws(i).Range("Q18").Left, _
Top:=ws(i).Range("Q18").Top, _
Width:=ws(i).Range("Q18:X18").Width, _
Height:=ws(i).Range("Q18:Q44").Height).Select
With ActiveChart
'Title
.HasTitle = True
.ChartTitle.Characters.Text = Range("A1")
'Primary Axis
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "No of Deals"
.SeriesCollection(1).Trendlines.Add Type:=xlMovingAvg
With .SeriesCollection(1).Trendlines(1)
.Border.ColorIndex = 11
End With
With .SeriesCollection(1).Trendlines(1).Format.Line
.Weight = 0.3
End With
End With
'Average Horizontal Line Line
With ActiveChart
With .SeriesCollection(2)
.ChartType = xlLine
.AxisGroup = 1
.Name = "Average"
.XValues = Range("s4:s12")
End With
End With
'Axis Title
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
Next
End Sub