bubleanalytics
Spammer
- Joined
- Jan 9, 2018
- Messages
- 1
Hi. I have a multi-series Bubble Chart (each data point is part of its own data series eg. ActiveChart.FullSeriesCollection(1).Select is first bubble, ActiveChart.FullSeriesCollection(2).Select is second bubble etc.)
I want to use a SELECT CASE statement so that a data point is coloured based on its value (eg. "Average", "Median") in column 1 of an excel spreadsheet. I get NO ERRORS but I CAN NOT get the following code to work on my chart even though it is the on;y chart on my active spreadsheet:
Sub ChartFormatBubblec()
Dim myChartObject As ChartObject
Dim mySrs As Series
Dim myPts As Points
With ActiveSheet
For Each myChartObject In .ChartObjects
For Each mySrs In myChartObject.Chart.SeriesCollection
Set myPts = mySrs.Points
With mySrs
For i = 1 To myPts.Count
SegmentValue = ActiveSheet.Cells(i + 1, 1).Value
Select Case SegmentValue
Case "Average"
myPts(myPts.Count).Format.Fill.ForeColor.RGB = RGB(300, 0, 0)
Case "Upper Limit"
myPts(myPts.Count).Format.Fill.ForeColor.RGB = RGB(0, 0, 0)
Case "Lower Limit"
myPts(myPts.Count).Format.Fill.ForeColor.RGB = RGB(0, 100, 0)
Case "1 Standard Deviation"
myPts(myPts.Count).Format.Fill.ForeColor.RGB = RGB(0, 0, 100)
Case "Median"
myPts(myPts.Count).Format.Fill.ForeColor.RGB = RGB(0, 0, 100)
Case "Mode"
myPts(myPts.Count).Format.Fill.ForeColor.RGB = RGB(0, 30, 0)
End Select
Next i
End With
Next
Next
End With
End Sub
I want to use a SELECT CASE statement so that a data point is coloured based on its value (eg. "Average", "Median") in column 1 of an excel spreadsheet. I get NO ERRORS but I CAN NOT get the following code to work on my chart even though it is the on;y chart on my active spreadsheet:
Sub ChartFormatBubblec()
Dim myChartObject As ChartObject
Dim mySrs As Series
Dim myPts As Points
With ActiveSheet
For Each myChartObject In .ChartObjects
For Each mySrs In myChartObject.Chart.SeriesCollection
Set myPts = mySrs.Points
With mySrs
For i = 1 To myPts.Count
SegmentValue = ActiveSheet.Cells(i + 1, 1).Value
Select Case SegmentValue
Case "Average"
myPts(myPts.Count).Format.Fill.ForeColor.RGB = RGB(300, 0, 0)
Case "Upper Limit"
myPts(myPts.Count).Format.Fill.ForeColor.RGB = RGB(0, 0, 0)
Case "Lower Limit"
myPts(myPts.Count).Format.Fill.ForeColor.RGB = RGB(0, 100, 0)
Case "1 Standard Deviation"
myPts(myPts.Count).Format.Fill.ForeColor.RGB = RGB(0, 0, 100)
Case "Median"
myPts(myPts.Count).Format.Fill.ForeColor.RGB = RGB(0, 0, 100)
Case "Mode"
myPts(myPts.Count).Format.Fill.ForeColor.RGB = RGB(0, 30, 0)
End Select
Next i
End With
Next
Next
End With
End Sub