Setting all the Chart Colours in VBA and also font sizes

[Michael Hudston]

I am creating all my charts in VBA, so they appear at a touch of a button. I
am looking to set the following properties, and cant seem to find them in the
help.

The Colours of the Columns in my Graph, so particular colums are always RED,
YELLOW, GREEN, etc.

The font size of the Chart Title and Legend.

 

[Michael Hudston]

As a reference this is the current VBA that I am using to display the chart.

Sub Cause_During_Corr_Acc_Click()

Dim chtChart As Chart

'Remove Existing Chart

ActiveSheet.ChartObjects.Delete

'Create a new chart.

Set chtChart = Charts.Add
Set chtChart = chtChart.Location(Where:=xlLocationAsObject, Name:="CHART")

With chtChart

.ChartType = xlColumnClustered

'Set data source range.

.SetSourceData Source:=Sheets("BASIC CHART DATA").Range("B17:C28"),
PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Cause Defined During Corrective Action"
.Axes(xlCategory).Delete

'The Parent property is used to set properties of
'the Chart.

With .Parent
.Top = Range("D2").Top
.Left = Range("D2").Left
.Name = "chart_CDCA"
End With

End With

End Sub

 

[Shiva]

Hi Michael,

I understand you are trying to fill the series bars on the chart, if so,
please find the code snippet

You need to update the RBG parameters are per your requirements.

For i = 1 To ActiveChart.Legend.LegendEntries.Count
ActiveChart.Legend.LegendEntries(i).LegendKey.Select

With Selection.Interior
Select Case i
Case 1
.Color = RGB(0, 51, 153)
Case 2
.Color = RGB(64, 102, 178)
Case 3
.Color = RGB(128, 153, 204)
Case 4
.Color = RGB(102, 102, 255)
Case 5
.Color = RGB(140, 140, 255)
Case 6
.Color = RGB(178, 178, 255)
Case 7
end select
Next

I hope this will help you!!!

Best regards,
Shiva

 

[Michael Hudston]

Shiva,

I guess there was meant to be a End With statement in their, but also it
just give me a Visual basic 400 error and no message.

Michael