VBA code to change Axis color

[pruleone]

Hi,

trying to build VBA code to change legend(s) color after changes in worksheet.
I have made following code but I received error - Run-time error '438' Object doesn't support this property or method.
By pressing debug it shows that there is something wrong with lines With Selection.Border and With Selection.Interior.Color = RBG (255,75,0).

I'm quite lost here, what to do?

    ActiveSheet.ChartObjects("Chart 1").Activate
    
    'On Error Resume Next
    
    ActiveChart.Legend.LegendEntries(1).Select
    With Selection.Border
        .ColorIndex = 2
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    Selection.Shadow = False
    With Selection.Interior.Color = RGB(255, 75, 0)
    End With

    ActiveChart.Legend.LegendEntries(2).Select
    With Selection.Border
        .ColorIndex = 2
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    Selection.Shadow = False
    With Selection.Interior.Color = RGB(255, 130, 0)
    End With
    
    ActiveChart.Legend.LegendEntries(3).Select
    With Selection.Border
        .ColorIndex = 2
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    Selection.Shadow = False
    With Selection.Interior.Color = RGB(255, 180, 0)
    End With

    ActiveChart.Legend.LegendEntries(4).Select
    With Selection.Border
        .ColorIndex = 2
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    Selection.Shadow = False
    With Selection.Interior.Color = RGB(236, 52, 0)
    End With

    ActiveChart.Legend.LegendEntries(5).Select
    With Selection.Border
        .ColorIndex = 2
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    Selection.Shadow = False
    With Selection.Interior.Color = RGB(140, 45, 140)
    End With

    ActiveChart.Legend.LegendEntries(6).Select
    With Selection.Border
        .ColorIndex = 2
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    Selection.Shadow = False
    With Selection.Interior.Color = RGB(77, 196, 17)
    End With

    ActiveChart.Legend.LegendEntries(7).Select
    With Selection.Border
        .ColorIndex = 2
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    Selection.Shadow = False
    With Selection.Interior.Color = RGB(0, 178, 221)
    End With

    ActiveChart.Legend.LegendEntries(8).Select
    With Selection.Border
        .ColorIndex = 2
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    Selection.Shadow = False
    With Selection.Interior.Color = RGB(230, 230, 230)
    End With

    ActiveChart.Legend.LegendEntries(9).Select
    With Selection.Border
        .ColorIndex = 2
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    Selection.Shadow = False
    With Selection.Interior.Color = RGB(200, 198, 196)
    End With

    ActiveChart.Legend.LegendEntries(10).Select
    With Selection.Border
        .ColorIndex = 2
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    Selection.Shadow = False
    With Selection.Interior.Color = RGB(162, 162, 162)
    End With

    ActiveChart.Legend.LegendEntries(11).Select
    With Selection.Border
        .ColorIndex = 2
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    Selection.Shadow = False
    With Selection.Interior.Color = RGB(128, 128, 128)
    End With

    ActiveChart.Legend.LegendEntries(12).Select
    With Selection.Border
        .ColorIndex = 2
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    Selection.Shadow = False
    With Selection.Interior.Color = RGB(102, 102, 102)
    End With

End Sub

 

[combo]

The code created by macro recorder frequently contains a pair:
[tt]Something.Select
With Selection.ChildObject
.Property1 = NewValue1
...
End With[/tt]
Usually this can be directly replaced with:
[tt]Something.ChildObject.Property1 = ChildNewValue1[/tt]
It's possible to use bigger structure:
[tt]With Something
.Property1 = NewValue1
.ChildObject.Property1 = NewValue1
End With[/tt]
You can simplify your code and remove settings that change nothing.

The With...End With requires object, you have statement, so the error.

combo

 

[pruleone]

Trying to figure out your suggestion but after trying so and so still will get same error message. Problem is with this second line.

Sub graafik()

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Legend.LegendEntries(1).Interior.Color = RGB(255, 75, 0)

End Sub

Most probably I'm doing something wrong but don't understand what

 

[pruleone]

Manage to solve this issue with following code:

Private Sub CommandButton1_Click()
    On Error Resume Next
  Dim C As Chart
  Dim i As Long, Max As Long
  Dim Red As Integer, Blue As Integer
  Set C = ActiveSheet.ChartObjects("Chart 1").Chart
  With C.Legend
      .LegendEntries(1).LegendKey.Interior.Color = RGB(250, 75, 0)
      .LegendEntries(1).LegendKey.Border.ColorIndex = 2
      .LegendEntries(2).LegendKey.Interior.Color = RGB(250, 130, 0)
      .LegendEntries(2).LegendKey.Border.ColorIndex = 2
      .LegendEntries(3).LegendKey.Interior.Color = RGB(250, 180, 0)
      .LegendEntries(3).LegendKey.Border.ColorIndex = 2
      .LegendEntries(4).LegendKey.Interior.Color = RGB(236, 52, 0)
      .LegendEntries(4).LegendKey.Border.ColorIndex = 2
      .LegendEntries(5).LegendKey.Interior.Color = RGB(140, 45, 140)
      .LegendEntries(5).LegendKey.Border.ColorIndex = 2
      .LegendEntries(6).LegendKey.Interior.Color = RGB(77, 196, 17)
      .LegendEntries(6).LegendKey.Border.ColorIndex = 2
      .LegendEntries(7).LegendKey.Interior.Color = RGB(0, 178, 221)
      .LegendEntries(7).LegendKey.Border.ColorIndex = 2
      .LegendEntries(8).LegendKey.Interior.Color = RGB(230, 230, 230)
      .LegendEntries(8).LegendKey.Border.ColorIndex = 2
      .LegendEntries(9).LegendKey.Interior.Color = RGB(200, 198, 196)
      .LegendEntries(9).LegendKey.Border.ColorIndex = 2
      .LegendEntries(10).LegendKey.Interior.Color = RGB(162, 162, 162)
      .LegendEntries(10).LegendKey.Border.ColorIndex = 2
      .LegendEntries(11).LegendKey.Interior.Color = RGB(128, 128, 128)
      .LegendEntries(11).LegendKey.Border.ColorIndex = 2
      .LegendEntries(12).LegendKey.Interior.Color = RGB(102, 102, 102)
      .LegendEntries(12).LegendKey.Border.ColorIndex = 2
  End With

End Sub