Change the color of the secondary axis title to the color of the data plotted

[RawlinsCross]

Good day,

I have two sets of data, one plotted on the primary vertical axis and the second plotted on the secondary vertical axis. The first data set is fixed, the second data set (on the secondary axis) is selected from a combobox list (13 possible choices).

I'm looking to change the color of the secondary axis title to that of the default excel color of the second data set (when plotted). How might I go about doing that?

Cheers

 

[Worf]

Good evening

o The code below does it manually. I understand you want this to function automatically, right?

o Is your chart embedded on a worksheet or standing alone on a chart sheet? This makes a difference when automating the process.

Sub Example()


Dim ch As Chart
Set ch = ActiveChart
ch.Axes(xlValue, xlSecondary).AxisTitle.Format.TextFrame2.TextRange.Font.Fill.ForeColor = _
ch.FullSeriesCollection(3).Format.Line.ForeColor


End Sub

 

[RawlinsCross]

Hi Worf,

Is there anything obvious I'm missing where I'm getting an "Object required" error on the following code?

'Color the secondary Axis
With MyChart1.Axes(xlValue, xlSecondary)
    If ComboBox2.ListIndex = 1 Then
    .AxisTitle.Format.TextFrame2.TextRange.Font.Fill.ForeColor = MyChart1.FullSeriesCollection(2).Format.Line.ForeColor
    ElseIf ComboBox2.ListIndex = 2 Then
    .AxisTitle.Format.TextFrame2.TextRange.Font.Fill.ForeColor = MyChart1.FullSeriesCollection(3).Format.Line.ForeColor
    ElseIf ComboBox2.ListIndex = 3 Then
    .AxisTitle.Format.TextFrame2.TextRange.Font.Fill.ForeColor = MyChart1.FullSeriesCollection(4).Format.Line.ForeColor
    ElseIf ComboBox2.ListIndex = 4 Then
    .AxisTitle.Format.TextFrame2.TextRange.Font.Fill.ForeColor = MyChart1.FullSeriesCollection(5).Format.Line.ForeColor
    ElseIf ComboBox2.ListIndex = 5 Then
    .AxisTitle.Format.TextFrame2.TextRange.Font.Fill.ForeColor = MyChart1.FullSeriesCollection(6).Format.Line.ForeColor
    ElseIf ComboBox2.ListIndex = 6 Then
    .AxisTitle.Format.TextFrame2.TextRange.Font.Fill.ForeColor = MyChart1.FullSeriesCollection(7).Format.Line.ForeColor
    ElseIf ComboBox2.ListIndex = 7 Then
    .AxisTitle.Format.TextFrame2.TextRange.Font.Fill.ForeColor = MyChart1.FullSeriesCollection(8).Format.Line.ForeColor
    ElseIf ComboBox2.ListIndex = 8 Then
    .AxisTitle.Format.TextFrame2.TextRange.Font.Fill.ForeColor = MyChart1.FullSeriesCollection(9).Format.Line.ForeColor
    ElseIf ComboBox2.ListIndex = 9 Then
    .AxisTitle.Format.TextFrame2.TextRange.Font.Fill.ForeColor = MyChart1.FullSeriesCollection(10).Format.Line.ForeColor
    ElseIf ComboBox2.ListIndex = 10 Then
    .AxisTitle.Format.TextFrame2.TextRange.Font.Fill.ForeColor = MyChart1.FullSeriesCollection(11).Format.Line.ForeColor
    ElseIf ComboBox2.ListIndex = 11 Then
    .AxisTitle.Format.TextFrame2.TextRange.Font.Fill.ForeColor = MyChart1.FullSeriesCollection(12).Format.Line.ForeColor
    ElseIf ComboBox2.ListIndex = 12 Then
    .AxisTitle.Format.TextFrame2.TextRange.Font.Fill.ForeColor = MyChart1.FullSeriesCollection(13).Format.Line.ForeColor
    ElseIf ComboBox2.ListIndex = 13 Then
    .AxisTitle.Format.TextFrame2.TextRange.Font.Fill.ForeColor = MyChart1.FullSeriesCollection(14).Format.Line.ForeColor
    End If
End With