Cantonalives
Member
Hi,
I have cobbled together some code from various sources and it almost works, well it does work in a limited fashion.
Excel Chart - So it does colour the last data point in the series, however I need the whole series to be totally transparent first and then have the first and last in the series colour a specific colour. For each, the label will be required inside and in white text.
Also is there an easy way to have all positive text labels to be above the item (red) and all negative labels below the respective data point?
Also at present I have to do it per chart, whereas would be good to apply to all charts.
Any help or guidance would be much appreciated.
I have cobbled together some code from various sources and it almost works, well it does work in a limited fashion.
Excel Chart - So it does colour the last data point in the series, however I need the whole series to be totally transparent first and then have the first and last in the series colour a specific colour. For each, the label will be required inside and in white text.
Also is there an easy way to have all positive text labels to be above the item (red) and all negative labels below the respective data point?
Also at present I have to do it per chart, whereas would be good to apply to all charts.
Sub ColourLastPoint()
Dim ws As Worksheet
Dim cht As Chart
Dim pt As Point
Dim sChartName As String
Dim Apoint As Long
sChartName = "Chart 1"
' worksheet that holds the chart
Set ws = Worksheets("T1")
' set reference to chart
Set cht = ws.ChartObjects(sChartName).Chart
' Clear previous bar fill
With cht.SeriesCollection(1)
Apoint = .Points.Count
End With
If Range("h1") <= Apoint Then
With cht.SeriesCollection(1)
Set pt = .Points(Range("h1"))
End With
pt.Format.Fill.Visible = msoFalse
pt.Format.Line.Visible = msoFalse
End If
' set reference to last point of the first series
With cht.SeriesCollection(1)
Set pt = .Points(.Points.Count)
End With
' set the colour of the point
pt.Format.Fill.Visible = msoTrue
pt.Format.Line.Visible = msoFalse
pt.Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorText2
pt.ApplyDataLabels
pt.DataLabel.Orientation = 90
pt.DataLabel.Font.ColorIndex = 2
Range("h1").Value = Range("h1").Value
End Sub
Any help or guidance would be much appreciated.
Last edited: