Log In
Register
Home
Power Query
January 30, 2020
Offline
Hi,
I have created a VBA Charting system with Pie charts, Lines, Scatter and columns.
All charts except for the column chart has only 1 colour (Blue). I would prefer different colours for different series
Can anyone verify if there is a code for the standard series colours
The code is below and the data block
End If
Dim MyChart2 As Chart
Dim ChartData2 As Range
Dim ChartIndex2 As Integer
Dim ChartName2 As String
ChartName2 = Sheet3.Range("A40") ' Correct
Application.ScreenUpdating = False
MyChart2.SeriesCollection(1).Name = ChartName2
MyChart2.SeriesCollection(1).Values = ChartData2
MyChart2.SeriesCollection(1).XValues = Sheet3.Range("A41:A45")
MyChart2.FullSeriesCollection(1).ApplyDataLabels
imageName3 = Application.DefaultFilePath & Application.PathSeparator & "TempChart.gif"
ActiveSheet.ChartObjects(1).Delete
Application.ScreenUpdating = True
| Incident Severity | Severity |
| Low | 3 |
| Medium | 1 |
| High | 1 |
| Extreme | 1 |
| Not Rated | 12 |
October 5, 2010
Offline
Hi Steve,
You can set the series colours like this
Sub ChangeSeriesColours()
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart
.SeriesCollection(1).Interior.Color = RGB(255, 0, 0) 'Red
.SeriesCollection(2).Interior.Color = RGB(0, 255, 0) ' Green
End With
End Sub
See attached.
Regards
Phil
Power Query
January 30, 2020
Offline
Thanks Phil,
This works well, however, with a pie chart all the series "Low, Medium, High ect are in different colors which is good.
But what you have set is changed the color to red as an example. Why cant I have, like the pie chart automatically have those different colors for individual series???
Steve
October 5, 2010
Offline
Hi Steve,
As always, illustrating what you are talking about saves me a lot of time figuring it out. Please provide a workbook.
I'm not entirely sure what you are referring to. If you have 2 series that you want to plot on a bar chart, then they will be in different colours.
If what you are talking about is that you are trying to plot 2 series on a scatter chart, against a 3rd series of values on the y-axis, then if you do this using Insert->Recommended Chart, Excel is probably disregarding the 3rd series and plotting Series 1 against Series 2. Hence you end up with only 1 colour for your points.
In such a case a scatter isn't the best chart.
Regards
Phil
Power Query
January 30, 2020
Offline
Hi Phil,
ALT F11 - Report Creator
Select ComboBox1 "Select A Chart"...Load Chart.
Image 3 & 4 show column chart which are blue "Incident Severity" & "Hazard type".
The pie charts 1,2 & 5 are in their proper colors so I dont need any mods for that.
Thanks
Steve
October 5, 2010
Offline
Hi Steve,
the columns in the column chart are all the same colour because they are not touching each other. There's no need to make them different colours as there is space between the columns.
The pie chart segments are different colours because this is necessary to indicate each segment - they are all touching.
You should use colour to highlight something on a chart. Making all the columns different colours can make it look very busy and hard on the eye. But if you really want to you can use this
Sub ColourPoints()
Dim MyChart2 As Chart
Dim i As Long
ActiveSheet.ChartObjects("Chart 9").Activate
For i = 1 To ActiveChart.FullSeriesCollection(1).Points.Count
ActiveChart.FullSeriesCollection(1).Points(i).Select
With Selection.Format.Fill
.ForeColor.RGB = RGB(Rnd() * 100, Rnd() * 100, 155)
End With
Next i
End Sub
We don't recommend pie charts anyway in most cases, they just aren't good for representing data.
https://www.myonlinetraininghu.....ie-chart
Regards
Phil
Power Query
January 30, 2020
Offline
Thanks Phill,
That works very well.
I understand the basis of colours of charts however my OH&S manager wanted the status Quo! - Just to keep him haoy.
But thanks for that you did well
Steve
1 Guest(s)
