Chart data label by color of text in cell

[brandon20]

Hi,

I was wondering if anyone could help me with some VBA code for a chart.

I need to put data labels on my chart, but I need the labels to reference a different range than the Data Series.
I have an add on called "XY chart Labels" that lets me do this, but I also need the data labels to match the text color and whether it is bold or underlined or all that in the cell. The XY chart Labels wont let me do this.
So I was wondering if someone could help me do this in VBA.

Here is an example, Lets say I have a bar chart of the Data Series but want to put data labels named by the Label range, Andd color the labels by the color of the text in the cell.
Thanks for any help

Name	Data Series	Label Range
Name1	10	AA
Name2	15	BB
Name3	20	CC
Name4	30	DD
Name5	40	EE
Name6	50	FF

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Domenic]

The following macro assumes that the active sheet contains the data, starting at A1, and that it contains the chart...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] ApplyCustomLabels()

    [COLOR=darkblue]Dim[/COLOR] rLabels [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] rCell [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] oSeries [COLOR=darkblue]As[/COLOR] Series
    [COLOR=darkblue]Dim[/COLOR] Cnt [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] rLabels = Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row)
    
    [COLOR=darkblue]Set[/COLOR] oSeries = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
    
    oSeries.HasDataLabels = [COLOR=darkblue]True[/COLOR]
    
    Cnt = 1
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] rCell [COLOR=darkblue]In[/COLOR] rLabels
        [COLOR=darkblue]With[/COLOR] oSeries.Points(Cnt).DataLabel
            .Text = rCell.Value
            .Font.Color = rCell.Font.Color
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        Cnt = Cnt + 1
    [COLOR=darkblue]Next[/COLOR] rCell
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

To refer to a specific chart on the sheet, let's say "Chart 1", replace...

Set oSeries = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)

with

Set oSeries = ActiveSheet.ChartObjects([COLOR=#ff0000]"Chart 1"[/COLOR]).Chart.SeriesCollection(1)

Hope this helps

 

[brandon20]

Amazing, yes this works great! Thank you

I am currently trying to figure out how to modify the code so it can color more an just one specific range, but I am a novice at this.

What if my Data actually looked like this, and so on, to data series 3, and 4 and 5....

Name	Data Series	Label Range	Data Series2	Label Range2
Name1	10	AA	20	GG
Name2	15	BB	50	DSF
Name3	20	CC	60	asdf
Name4	30	DD	90	gfdg
Name5	40	dasf	20	hdf
Name6	50	FF	20	ad

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Is there a way to modify the code to look in more than just C2:C but also E2:E and so on..

again, Thanks!

 

[Domenic]

Try...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] ApplyCustomLabels()

    [COLOR=darkblue]Dim[/COLOR] rLabelRange [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] rCell [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] oSeries [COLOR=darkblue]As[/COLOR] Series
    [COLOR=darkblue]Dim[/COLOR] ColIndx [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] SrsIndx [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] PntIndx [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] rLabelRange = Range("B2:E" & Cells(Rows.Count, "A").End(xlUp).Row) [COLOR=#008000]'adjust the ending column reference accordingly[/COLOR]
    
    SrsIndx = 1
    [COLOR=darkblue]For[/COLOR] ColIndx = 1 [COLOR=darkblue]To[/COLOR] rLabelRange.Columns.Count [COLOR=darkblue]Step[/COLOR] 2
        [COLOR=darkblue]Set[/COLOR] oSeries = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(SrsIndx)
        oSeries.HasDataLabels = [COLOR=darkblue]True[/COLOR]
        PntIndx = 1
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] rCell [COLOR=darkblue]In[/COLOR] rLabelRange.Offset(, ColIndx).Resize(, 1).Cells
            [COLOR=darkblue]With[/COLOR] oSeries.Points(PntIndx).DataLabel
                .Text = rCell.Value
                .Font.Color = rCell.Font.Color
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
            PntIndx = PntIndx + 1
        [COLOR=darkblue]Next[/COLOR] rCell
        SrsIndx = SrsIndx + 1
    [COLOR=darkblue]Next[/COLOR] ColIndx
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[brandon20]

Hi,

Thanks! that works. My only issue is that in my actual data table the series starts on column K, (as opposed to B). And the label range is 2 columns before (column I) as opposed to 1 column in front (column C).
I tried to use PntIndex =PntIndex +1 and change it to PntIndex =PntIndex - 2 but that did not work.
And Im realizing there are other issues as well, (that I dont want to label every range.)

Because of all these issues I will probably use the first code you gave and just adjust it to label the columns I need.

Thanks again.