Chnging Chart Datalabel Colour Based on Cell Value

[dave3009]

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

 

[dave3009]

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.

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