Charts won't maintain series color when only changing values with VBA

[MoshiM]

When I attempt to use the following to update series values using an array, The series don't maintain their coloration and defaults to one of the available presets.

With Chart_Dictionary.Item("Non-C").Chart

    For L = 1 To 4

        .FullSeriesCollection(L).XValues = Date_Range

        Select Case L

            Case 1: .FullSeriesCollection("1").Values = This_Column(AR, 4) 
            Case 2: .FullSeriesCollection("2").Values = This_Column(AR, 5) 
            Case 3: .FullSeriesCollection("3").Values = This_Column(AR, 3) 
            Case 4: .FullSeriesCollection("4").Values = This_Column(AR, 6) 
            
        End Select

    Next L

End With

 

[MoshiM]

Another problem is that it is unhiding hidden series

Full macro but using a range instead of an array.

Private Sub Update_Charts() 'changing Chart Data

Dim TT As Long, AR As Range, Date_Range As Range, CN As Byte, DD As Byte, Item As ChartObject, Enter_Function As Boolean

CN = Variable_Sheet.ListObjects("Saved_Variables").DataBodyRange.Cells(2, 2).Value2

Application.ScreenUpdating = False

Set AR = ThisWorkbook.Worksheets(Chart_Sheet.Sheet_Selection.Value).range("A1").ListObject.DataBodyRange '.SpecialCells(xlCellTypeVisible)

'Chart_Sheet.Sheet_Selection IS A COMBOBOX


Set Date_Range = AR.Columns(1)

For Each Item In Chart_Sheet.ChartObjects
    
    With Item.Chart
        
        Select Case Item.Name
        
            Case "Net"
    
                For TT = 1 To 4
            
                    .FullSeriesCollection(TT).XValues = Date_Range
                    
                    Select Case TT
                                                                            
                        Case 1: .FullSeriesCollection("Commercial").Values = AR.Columns(CN)            'Commercial 
                        Case 2: .FullSeriesCollection("Non-Commercial").Values = AR.Columns(CN + 1)  'Non-Commercial
                        Case 3: .FullSeriesCollection("Non-Reportable").Values = AR.Columns(CN + 2)  'Non-Reportable 
                        Case 4: .FullSeriesCollection("OI").Values = AR.Columns(3)     'Open Interest
            
                    End Select
            
                Next TT
                
            Case "Commercial "
            
                For TT = 1 To 3
            
                    .FullSeriesCollection(TT).XValues = Date_Range
            
                    Select Case TT
            
                        Case 1: .FullSeriesCollection("Commercial Long").Values = AR.Columns(7)  'Commercial L
                        Case 2: .FullSeriesCollection("Commercial Short").Values = AR.Columns(8)  'Commercial S
                        Case 3: .FullSeriesCollection("OI").Values = AR.Columns(3)  'OI
            
                    End Select
            
                Next TT
            
            Case "Non-Commercial Positions"
                    
                For TT = 1 To 4
            
                    .FullSeriesCollection(TT).XValues = Date_Range
            
                    Select Case TT
            
                        Case 1: .FullSeriesCollection("Non-Commercial Long").Values = AR.Columns(4)  'N-Commercial L
                        Case 2: .FullSeriesCollection("Non-Commercial Short").Values = AR.Columns(5)  'N-Commercial S
                        Case 3: .FullSeriesCollection("OI").Values = AR.Columns(3)  'OI
                        Case 4: .FullSeriesCollection("Non-Commercial S").Values = AR.Columns(6)  'N-Spread
                        
                    End Select
            
                Next TT
                
            Case "Commercial % "
            
                For TT = 1 To 2
            
                    .FullSeriesCollection(TT).XValues = Date_Range
            
                    Select Case TT
            
                        Case 1: .FullSeriesCollection(OI-Long (All)").Values = AR.Columns(27)  
                        Case 2: .FullSeriesCollection("OI-Short (All)").Values = AR.Columns(28) 
            
                    End Select
            
                Next TT
            
            Case "Non-Commercial % OI"
            
                For TT = 1 To 2
            
                    .FullSeriesCollection(TT).XValues = Date_Range
            
                    Select Case TT
            
                        Case 1: .FullSeriesCollection("% of OI-Noncommercial-Long (All)").Values = AR.Columns(24)  'N-Commercial L OI%
                        Case 2: .FullSeriesCollection("% of OI-Noncommercial-Short (All)").Values = AR.Columns(25)  'N-Commercial S OI%
            
                    End Select
            
                Next TT
        
            Case "MoI"
    
                DD = CN + 12: Enter_Function = True
        
            Case "6M"
    
                DD = CN + 10: Enter_Function = True
                
            Case "3Y"
                
                DD = CN + 11: Enter_Function = True
                
        End Select
        
        If Enter_Function = True Then Call Just_One_Series(Date_Range, Item, DD, AR)
        
        Enter_Function = False
  
    End With

Next Item

Application.ScreenUpdating = True

End Sub

Private Sub Just_One_Series(Date_Range As Range, This_Chart As Variant, ColumnN As Byte, ART As Range)

With This_Chart.Chart

    .FullSeriesCollection(1).XValues = Date_Range
    .FullSeriesCollection(1).Values = ART.Columns(ColumnN)
    
End With

End Sub