Colour first and last in a particular series

[Cantonalives]

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.

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.