Color chart caption with VBA

[DougTrajano]

Guys.


I need some help to create a macro that chooses the color of the graph. I have several graphs that I will use here as an example, name of employees. In some graphics can have Fernando, Fábio, etc, others can have only Fernando. Not all employees are present on all graphics.


I need that in all the graphs the "Fernando" is of the red color and the "Fábio" in all the graphs that it appears is of the color blue.


Here is the code I already have, but it isn't working.

Sub macro_grafico()
If ActiveSheet.ChartObjects("Gráfico 20").Activate Then
ActiveChart.SeriesCollection("Fernando").Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 255, 0)
.Transparency = 0
.Solid
End With
ElseIf ActiveChart.SeriesCollection("Fabio").Select Then
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 235, 158)
.Transparency = 0
.Solid
End With
ElseIf ActiveChart.SeriesCollection("Priscilla").Select Then
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(204, 51, 0)
.Transparency = 0
.Solid
End With
ElseIf ActiveChart.SeriesCollection("Bruna").Select Then
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(49, 133, 156)
.Transparency = 0
.Solid
End With
ElseIf ActiveChart.SeriesCollection("Bárbara").Select Then
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(102, 0, 102)
.Transparency = 0
.Solid
End With
ElseIf ActiveChart.SeriesCollection("Julio").Select Then
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(195, 214, 155)
.Transparency = 0
.Solid
End With
ElseIf ActiveChart.SeriesCollection("Henrique").Select Then
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(204, 193, 218)
.Transparency = 0
.Solid
End With
ElseIf ActiveChart.SeriesCollection("Cassiano").Select Then
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(55, 96, 146)
.Transparency = 0
.Solid
End With
ElseIf ActiveChart.SeriesCollection("Leandro").Select Then
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(64, 49, 82)
.Transparency = 0
.Solid
End With
ElseIf ActiveChart.SeriesCollection("Jorge").Select Then
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(33, 89, 104)
.Transparency = 0
.Solid
End With
ElseIf ActiveChart.SeriesCollection("Guilherme").Select Then
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(127, 127, 127)
.Transparency = 0
.Solid
End With
ElseIf ActiveChart.SeriesCollection("Lucas").Select Then
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(119, 147, 60)
.Transparency = 0
.Solid
End With
ElseIf ActiveChart.SeriesCollection("Pedro").Select Then
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(1, 13, 255)
.Transparency = 0
.Solid
End With
ElseIf ActiveChart.SeriesCollection("Sergio").Select Then
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(152, 72, 7)
.Transparency = 0
.Solid
End With
ElseIf ActiveChart.SeriesCollection("Gustavo").Select Then
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(155, 187, 89)
.Transparency = 0
.Solid
End With
ElseIf ActiveChart.SeriesCollection("Daniele").Select Then
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 0, 0)
.Transparency = 0
.Solid
End With
End If
End Sub

I really appreciate your help.

Tks

[mdmackillop]

Can you post a sample workbook Go Advanced / Manage Attachments

[mdmackillop]

Does this help?

[DougTrajano]

Sure man, I attached a example in this post. I will explain more about my problem. See a image below.

Capture.JPG

I would like that Fabio always have a orange and Luiza cannot be orange in a graph that Fabio doesn't exist.

Luiza need to have a own color. Make sense for you?

Tks bro

[mdmackillop]

Set your colours as required on Sheet 2

Option Explicit


Sub Colours()
    Dim Cht As ChartObject
    Dim rPatterns As Range
    Set rPatterns = Sheets("Sheet2").Range("A1:A16")
    For Each Cht In ActiveSheet.ChartObjects
        Call ColorBySeriesName(Cht, rPatterns)
    Next
End Sub

Sub ColorBySeriesName(Cht, rPatterns)
    Dim iSeries As Long
    Dim rSeries As Range
    
    With Cht.Chart
        For iSeries = 1 To .SeriesCollection.Count
            Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, _
            LookAt:=xlWhole)
            If Not rSeries Is Nothing Then
                .SeriesCollection(iSeries).Format.Fill.ForeColor.RGB = _
                rSeries.Interior.Color
            End If
        Next
    End With
End Sub

[DougTrajano]

Hey buddy.

Sorry for delayed my reply.

So, this worked fine in Excel, but now, I need make it for PowerPoint?

I have a lot of graphs in PowerPoint and I refresh all datas, but colors in PowerPoint doesn't correspond with excel file.

We can adapt this macro to PowerPoint?

Tks a lot any help with it. =)

[mdmackillop]

I don't know enough about Powerpoint. Post a question in that forum with a link to here.