Please HELP!!! I would really appreciate it if someone could have a look at the code and offer any suggestions. THANK YOU in advance.
I'm new to VBA so I can use all the help I can get right now.
I have 8 charts (maybe more will be added later) in the same worksheet ("Dashboard"). Each chart either has a minimum of 6 and a max of 7 series plus it may or may not have a "Target" line. Each series has 8 data points. Naming of series is consistant in all the charts (some charts might not have all the series or target line though)
I need to colour code the series based on the name of the series (ie. "cat", "dog", "target", etc). I want to be able to write a macro so that it does this automatically for all the charts at once.
Can someone help please?
I have the following code that I've been working on, but it's not giving me what I want.
----
Sub DoAll()
Dim objCht As ChartObject
For Each objCht In Sheets("Dashboard").ChartObjects
Graphs objCht
Next objCht
End Sub
Sub Graphs(Cht As ChartObject)
'
' Graphs Macro
With Cht.Chart
With .ChartTitle
.AutoScaleFont = True
With .Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
End With
With .Axes(xlValue).TickLabels
.AutoScaleFont = True
With .Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 5
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
End With
With .Axes(xlCategory).TickLabels
.AutoScaleFont = True
With .Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 5
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
End With
With .Axes(xlValue).AxisTitle
.AutoScaleFont = True
With .Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = 5
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
End With
With .SeriesCollection("All")
.MarkerSize = 3
.MarkerStyle = 8
.MarkerBackgroundColor = RGB(55, 55, 150)
.MarkerForegroundColor = RGB(55, 55, 150)
.Format.Line.Weight = 1.25
.Border.Color = RGB(55, 55, 150)
End With
With .SeriesCollection("Cat")
.MarkerSize = 3
.MarkerStyle = 2
.MarkerBackgroundColor = RGB(56, 145, 167)
.MarkerForegroundColor = RGB(56, 145, 167)
.Format.Line.Weight = 1.25
.Border.Color = RGB(56, 145, 167)
End With
With .SeriesCollection("Dog")
.MarkerSize = 3
.MarkerStyle = 1
.MarkerBackgroundColor = RGB(195, 134, 13)
.MarkerForegroundColor = RGB(195, 134, 13)
.Format.Line.Weight = 1.25
.Border.Color = RGB(195, 134, 13)
End With
With .SeriesCollection("Mouse")
.MarkerSize = 3
.MarkerStyle = 3
.MarkerBackgroundColor = RGB(192, 0, 0)
.MarkerForegroundColor = RGB(192, 0, 0)
.Format.Line.Weight = 1.25
.Border.Color = RGB(192, 0, 0)
End With
With .SeriesCollection("Fish")
.MarkerSize = 3
.MarkerStyle = 4
.MarkerBackgroundColor = RGB(103, 166, 60)
.MarkerForegroundColor = RGB(103, 166, 60)
.Format.Line.Weight = 1.25
.Border.Color = RGB(103, 166, 60)
End With
With .SeriesCollection("Turtle")
.MarkerSize = 3
.MarkerStyle = 5
.MarkerBackgroundColor = RGB(145, 79, 171)
.MarkerForegroundColor = RGB(145, 79, 171)
.Format.Line.Weight = 1.25
.Border.Color = RGB(145, 79, 171)
End With
With .SeriesCollection("Pig")
.MarkerSize = 3
.MarkerStyle = 6
.MarkerBackgroundColor = RGB(119, 99, 53)
.MarkerForegroundColor = RGB(119, 99, 53)
.Format.Line.Weight = 1.25
.Border.Color = RGB(119, 99, 53)
End With
With .SeriesCollection("Target")
.MarkerSize = 0
.MarkerStyle = 0
.Format.Line.Weight = 1.25
.Border.Color = RGB(0, 0, 0)
End With
.Parent.RoundedCorners = True
.Parent.Shadow = False
End With
End Sub
Bookmarks