My fascination for charts and vba continues (see previous posts). This post demonstrates how to highlight a group of values and how to add corresponding data labels. I think the animated picture below says it all.

Dynamic data labels in a chart1

How I created this chart

Add a drop down list to cell I 18

  1. Select cell I18
  2. Go to tab "Data"
  3. Click "Data Validation" button
  4. Select List
  5. Type Africa, Asia, Europe, North America, South America in source:
    data validation dynamic data labels
  6. Click OK

Insert a scatter chart

  1. Select the numbers in table table1
    table - dynamic data labels
  2. Go to tab "Insert"
  3. Click "Scatter" chart button
  4. Click "Scatter with only markers"

Change y and x axis formatting.

VBA

  1. Right click on sheet name
  2. Click on "View Code"
    table - vb editor1
  3. Paste code (see below) to sheet module

    table - vb editor

  4. Exit VB Editor
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Integer
If Target.Address = "$I$18" Then
    With ActiveSheet.ChartObjects(1).Chart
        For r = 1 To Range("Table1[Country]").Rows.Count
            If Range("Table1[Region]").Cells(r).Value = Range("$I$18") Then
                .SeriesCollection(1).Points(r).Format.Fill.ForeColor.RGB = RGB(0, 0, 255)
                .SeriesCollection(1).Points(r).ApplyDataLabels
                .SeriesCollection(1).Points(r).DataLabel.Text = Range("Table1[Country]").Cells(r).Value
            Else
                .SeriesCollection(1).Points(r).Format.Fill.ForeColor.RGB = RGB(198, 198, 198)
                On Error Resume Next
                .SeriesCollection(1).Points(r).DataLabel.Delete
                On Error GoTo 0
            End If
        Next r
    End With
End If
End Sub

Download excel *.xlsm file
Dynamic data labels in a chart.xlsm