anna82marie
New Member
- Joined
- Jan 22, 2014
- Messages
- 15
- Office Version
- 365
- Platform
- Windows
- MacOS
I've created a pie chart (it has to be a pie chart unfortunately), with data labels and values.
I need a code to automatically colour the segments red, amber or green, according to the value; 1-2 green, 3-4 amber and 5 red.
So far I have this, but it's analysing the category instead of the value; how do I change it?
Sub Pie_Colour()
Sheets("Dashboard").Select
ActiveSheet.ChartObjects("Chart 27").Activate
ActiveChart.PlotArea.Select
Dim i As Long
Dim PointText As String
With ActiveChart.SeriesCollection(1)
For i = 1 To .Points.Count
With .Points(i)
.ApplyDataLabels Type:= _
xlDataLabelsShowLabel, AutoText:=True
ValueText = .DataLabel.Text
Select Case ValueText
Case "5"
.Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
Case "4"
.Format.Fill.ForeColor.RGB = RGB(255, 255, 0)
Case "3"
.Format.Fill.ForeColor.RGB = RGB(255, 255, 0)
Case "2"
.Format.Fill.ForeColor.RGB = RGB(0, 255, 0)
Case "1"
.Format.Fill.ForeColor.RGB = RGB(0, 255, 0)
End Select
.HasDataLabel = False
End With
Next i
End With
End Sub
Please help; I've got a deadline to meet by Thursday morning!
I need a code to automatically colour the segments red, amber or green, according to the value; 1-2 green, 3-4 amber and 5 red.
So far I have this, but it's analysing the category instead of the value; how do I change it?
Sub Pie_Colour()
Sheets("Dashboard").Select
ActiveSheet.ChartObjects("Chart 27").Activate
ActiveChart.PlotArea.Select
Dim i As Long
Dim PointText As String
With ActiveChart.SeriesCollection(1)
For i = 1 To .Points.Count
With .Points(i)
.ApplyDataLabels Type:= _
xlDataLabelsShowLabel, AutoText:=True
ValueText = .DataLabel.Text
Select Case ValueText
Case "5"
.Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
Case "4"
.Format.Fill.ForeColor.RGB = RGB(255, 255, 0)
Case "3"
.Format.Fill.ForeColor.RGB = RGB(255, 255, 0)
Case "2"
.Format.Fill.ForeColor.RGB = RGB(0, 255, 0)
Case "1"
.Format.Fill.ForeColor.RGB = RGB(0, 255, 0)
End Select
.HasDataLabel = False
End With
Next i
End With
End Sub
Please help; I've got a deadline to meet by Thursday morning!