trouble setting colors

  • Thread starter Thread starter feh
  • Start date Start date
F

feh

I'd like to set the color of the datalabels for my series to be the
same as the series itself.

The following does not work. It would seem that the color codes used
by .interior.color of the series are different from those used for the
font of a selection of a datalabel.

Dim myChrt As Chart
Set myChrt = ActiveChart
Dim oSer As Series
Dim dlabel As DataLabel
Dim oSerColor As String

For Each oSer In myChrt.SeriesCollection

oSerColor = oSer.Interior.Color

oSer.HasDataLabels = True
For Each dlabel In oSer.DataLabels
dlabel.Select
Selection.Font.Color = oSerColor
Next

Next


I'm using office 2007 btw.

Thanks,
Charles
 
Hi Charles,

Following appears to work in xl2007.

Note: oSerColor As Long not string

Dim myChrt As Chart
Set myChrt = ActiveChart
Dim oSer As Series
Dim dlabel As DataLabel
Dim oSerColor As Long

For Each oSer In myChrt.SeriesCollection
oSerColor = oSer.Border.Color
oSer.HasDataLabels = True
For Each dlabel In oSer.DataLabels
dlabel.Font.Color = oSerColor
Next dlabel
Next oSer
 
Hi again Charles,

Just a little added info. In lieu of activating the chart and then setting a
variable to the active chart you can use the following line of code and there
is no need to activate the chart.

Set myChrt = ActiveSheet.ChartObjects("Chart 3").Chart

or

Set myChrt = Sheets("Sheet1").ChartObjects("Chart 3").Chart


If you don't know the name of the chart.
Select any cell on the worksheet (to deactivate the chart)
Turn on the macro recorder.
Select the chart.
Turn off the macro recorder
You should have a recorde line of code like the following.

ActiveSheet.ChartObjects("Chart 3").Activate
 
Back
Top