Chnging Chart Datalabel Colour Based on Cell Value

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
Hi Everyone

I'm lokking to see if there is a way to change the colour of a chart datalabel point based on the value of a cell.

For example if the cell in question is above 100% I'd like the datalabel to turn green if it is below then it'd be red.

I've search and tried a few examples but can't get them to work, I've run the macro recorder and tried to alter it some but still nothing.

If you'd like to see what I've tried so far then I'll be happy to post. I have around 90 data labels I wish to alter by value so any help on making the task easier would be greatly appreciated.

Thanks in advance


Dave
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
Hi Guys

Here is the code I've been working using the Macro Recorder. It's a bit longwinded I'm afraid so I was hoping someone might advise how to reduce it. Also it doesn't work quite the way I want it. In this example data I am just change the DataLabel font colour when the cell is above 100%, This doesn't really happen.

Code:
Sub DataLabelColour()
If ActiveSheet.Range("C4").Value >= 1 Then
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).DataLabels.Select
    ActiveChart.SeriesCollection(1).Points(1).DataLabel.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 4
        .Background = xlAutomatic
    End With
If ActiveSheet.Range("C4").Value < 1 Then
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).DataLabels.Select
    ActiveChart.SeriesCollection(1).Points(1).DataLabel.Select

    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 3
        .Background = xlAutomatic
    End With
If ActiveSheet.Range("C5").Value >= 1 Then
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).DataLabels.Select
    ActiveChart.SeriesCollection(1).Points(2).DataLabel.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 4
        .Background = xlAutomatic
    End With
If ActiveSheet.Range("C5").Value < 1 Then
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).DataLabels.Select
    ActiveChart.SeriesCollection(1).Points(2).DataLabel.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 3
        .Background = xlAutomatic
    End With
If ActiveSheet.Range("C6").Value >= 1 Then
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).DataLabels.Select
    ActiveChart.SeriesCollection(1).Points(3).DataLabel.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 4
        .Background = xlAutomatic
    End With
If ActiveSheet.Range("C6").Value < 1 Then
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).DataLabels.Select
    ActiveChart.SeriesCollection(1).Points(3).DataLabel.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 3
        .Background = xlAutomatic
    End With
End If
End If
End If
End If
End If
End If
End Sub

Any help would be greatly appreciated as I intend to apply this to around 90 or so data sets.


Thanks


Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,109,362
Messages
5,528,257
Members
409,811
Latest member
pjwhyman

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top