Change colour of chart series

hey

I'm attempting to write a code that will change the colour of a series in a chart based on the name of each series.

i.e. I have Roads, Water and Rail as three of my series names. can i somehow assign the colours yellow, blue and red to these series using macro code.

Thanks Heaps

Perhaps this can be a start you can adapt to your workbook. This assumes a chart object in the worksheet as opposed to a Chart Sheet.

Option Explicit

Sub ChangeSeriesColor()

Dim co As ChartObject, x, y, z

    x = Range("I1").Value
    y = Range("I2").Value
    z = Range("I3").Value
    
    On Error Resume Next
    
    For Each co In ActiveSheet.ChartObjects
        co.Chart.SeriesCollection(1).Interior.ColorIndex = x
        co.Chart.SeriesCollection(2).Interior.ColorIndex = y
        co.Chart.SeriesCollection(3).Interior.ColorIndex = z
    Next co
    
End Sub

You could run the code by using the worksheet change event to call it. Thus, by entering numeric values in cells, automatically change the color. See cells I1:I3 in the attached.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("I1:I3")) Is Nothing Then
        Run "ChangeSeriesColor"
    End If

End Sub

Hi

This is not quite what I'm looking for.

I have applied the following code to change the colours on pie charts:

     ' Iterates through all pie charts in the dashboard and apply colours to the appropriate legends
    Dim savePtLabel As String
    Dim ThisPt As String
     
    Dim NumPoints As Integer
    Dim x As Integer
     
    Dim pie As ChartObject
    Sheets("Revenue By Sector").Select
    For Each pie In ActiveSheet.ChartObjects
         
         ' Check that the current chart object is a pie chart
        If pie.Chart.ChartType = xlPie Then
             
            NumPoints = pie.Chart.SeriesCollection(1).Points.Count
            For x = 1 To NumPoints
                 
                 '  Save the label currently  attached to the current slice
                If pie.Chart.SeriesCollection(1).Points(x).HasDataLabel = True Then
                    savePtLabel = pie.Chart.SeriesCollection(1).Points(x).DataLabel.Text
                Else
                    savePtLabel = ""
                End If
                 
                 '  Assign a new data label of just the point  name
                pie.Chart.SeriesCollection(1).Points(x).ApplyDataLabels Type:= _
                xlDataLabelsShowLabel, AutoText:=True
                ThisPt = pie.Chart.SeriesCollection(1).Points(x).DataLabel.Text
                 
                 ' Based on the label of this slice, set the color
                Select Case ThisPt
                Case "Water"
                    pie.Chart.SeriesCollection(1).Points(x).Interior.ColorIndex = 41
                Case "Roads"
                    pie.Chart.SeriesCollection(1).Points(x).Interior.ColorIndex = 19
                Case "Rail"
                    pie.Chart.SeriesCollection(1).Points(x).Interior.ColorIndex = 22
                Case "Ports"
                    pie.Chart.SeriesCollection(1).Points(x).Interior.ColorIndex = 25
                Case "Industrial"
                    pie.Chart.SeriesCollection(1).Points(x).Interior.ColorIndex = 30
                Case "Resource Engineering"
                    pie.Chart.SeriesCollection(1).Points(x).Interior.ColorIndex = 35
                Case "Airports"
                    pie.Chart.SeriesCollection(1).Points(x).Interior.ColorIndex = 34
                Case "Energy"
                    pie.Chart.SeriesCollection(1).Points(x).Interior.ColorIndex = 38
                Case "Oil & Gas"
                    pie.Chart.SeriesCollection(1).Points(x).Interior.ColorIndex = 38
                Case "Water Treatment"
                    pie.Chart.SeriesCollection(1).Points(x).Interior.ColorIndex = 41
                Case "Water Transmission"
                    pie.Chart.SeriesCollection(1).Points(x).Interior.ColorIndex = 41
                Case "Wastewater Treatment"
                    pie.Chart.SeriesCollection(1).Points(x).Interior.ColorIndex = 41
                Case Else
                     ' Aroo! The label of the current slice doesn't match any expected labels
                    pie.Chart.SeriesCollection(1).Points(x).Interior.ColorIndex = 1
                End Select
                 
                 ' Return the label to it's original state
                pie.Chart.SeriesCollection(1).Points(x).ApplyDataLabels Type:=xlDataLabelsShowValue, AutoText:=True
                 
            Next x
        End If
    Next

I would like to apply somethin similar to a 'Stacked Area' Chart

Cheers

DUT

p.s. sorry I don't know how to add specific windows in my message for the code