Something like this,
Sub ColorChart()
Dim vntPrograms As Variant
Dim vntColors As Variant
Dim lngIndex As Long
Dim colProgramColors As Collection
Dim rngDataPoints As Range
vntPrograms = Array("FP", "STD-HIV", "WIC", "PN", "IZ", "NP")
vntColors = Array(vbGreen, vbMagenta, vbRed, vbYellow, vbBlack, vbBlue)
Set colProgramColors = New Collection
For lngIndex = LBound(vntPrograms) To UBound(vntPrograms)
colProgramColors.Add vntColors(lngIndex), vntPrograms(lngIndex)
Next
Set rngDataPoints = Range("C4:C72")
With ActiveChart
For lngIndex = 1 To rngDataPoints.Rows.Count
If rngDataPoints.Cells(lngIndex, 1) <> "" Then
.SeriesCollection(1).Points(lngIndex).Format.Fill.ForeColor.RGB = colProgramColors(rngDataPoints.Cells(lngIndex, 1))
End If
Next
End With
End Sub
You will need to adjust the range that contains the Program name.
Also the actual program names and the colour you want assigned.
Bookmarks