How do I use VBA to add and format data labels

I have roughly 50 bars in an excel chart and below is some VBA that someone from experts exchange helped me to custom format the colors of the bars.  I would like to add to this and add data labels 8 pt aerial font with one decimal place.  Below is the code for one of the 50 points on the graph.  

I am happy to edit each one if necessary, but is it possible to create a macro that affects all the bars in terms of data labels.
Sub FormatChartPoints()
   Dim cht As Chart
   Dim n As Long
   Set cht = Charts("quintile chart")
   With cht.SeriesCollection(1)
      With .Points(1).Format
      With .Fill
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 51, 204)
         .Transparency = 0
         .Solid
      End With
      With .Line
         .Visible = msoTrue
         .ForeColor.RGB = RGB(0, 0, 0)
         .ForeColor.TintAndShade = 0
    End With
    End With

Open in new window

majervisAsked:
Who is Participating?
 
dlmilleCommented:
Just a couple additional lines - ensure datalabels are on, then set the numberformat and font.size:

 
Sub FormatChartPoints()
Dim cht As Chart
Dim n As Long

    Set cht = Charts("quintile chart")
   
    With cht.SeriesCollection(1)
        With .Points(1).Format
            With .Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(0, 51, 204)
            .Transparency = 0
            .Solid
            End With
            With .Line
            .Visible = msoTrue
            .ForeColor.RGB = RGB(0, 0, 0)
            .ForeColor.TintAndShade = 0
            End With
        End With
        .HasDataLabels = True
        .DataLabels.NumberFormat = "##.0"
        .DataLabels.Font.Size = 8
    End With
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.