Changing the labelcolor in a chart based on source text color

[Rhalecki]

I have data. 2 columns a2:b5

each row has a different font color


apple 4
banana 3
peach 2
pear 1

I want the label in a chart to march the font color of the source cell

So in a chart I'd have 4 labels:
"apple"
"banana"
"peach"
"pear"

I can't see how to do it. When i select the albels it's "all or none". I can change ll the labels to a color. But, I want each individual label to be a diferent color.

I suspect the answer is in VBA, but I'm not quite sure how to go about it.

Thanks

 

[Sal Paradise]

If you mean data labels (not axis labels), then this is doable. Make a column chart of your data, add labels, and then go to the "Developer" tab and click "record macro". Click the labels once, and then a single label one more time after a short pause. You should only have 1 out of 4 labels selected. Then you can change the color of that label, and click "stop recording". This should give you some code like this (sorry, Japanese excel):

    ActiveSheet.ChartObjects("グラフ 1").Activate
    ActiveChart.SeriesCollection(1).DataLabels.Select
    ActiveChart.SeriesCollection(1).Points(1).DataLabel.Select
    With Selection.Format.TextFrame2.TextRange.Font.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
        .Solid
    End With

So you can select a single label by using:

SeriesCollection(1).Points(1).DataLabel.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 0, 0)

You can loop through the various points by using a for loop, and you can get the specific RGB color from a cell by using VBA to look that up.

I don't know how familiar you are with VBA, and what your actual data/chart object are like, so I won't write the code to do it, but that should get you started if you want to learn yourself.

 

[Rhalecki]

Sal,
This is helpful. But, I realize now that I want to change the axis labels. Is there anyway to do that?
Rich

 

[Sal Paradise]

To the best of my knowledge, no.

You can create dummy series using something like this though:
Arbitrary Axis Scale

And then color those dummy series labels with VBA like above.

The other option would be to use the camera tool in a tricky way to make regular cells look like axis labels (this will also give you coloring without VBA):
What is Excel Camera tool and how to use it? [MS Excel Tutorial] | Chandoo.org - Learn Microsoft Excel Online