PowerPoint Chart Colors 1

[mblaster]

HELP!!!! Company changed colors for charts both in PowerPoint & Excel. How do I update existing charts with the new colors? I tried recording a PowerPoint Macro but it does not retain the color changes. PLEASE HELP!!

Thanks in advance!

 

[SkipVought]

Hi,

What part(s) of charts is/are changing color?

From what to what?

Are these Embedded Excel charts or Office Charts (Microsoft Graph)?

Are they linked to Excel Workbooks?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[mblaster]

What part(s) of charts is/are changing color?
Series FillColor for: Bar/Pie/Line

From what to what?
From whatever the current fill color to specific RGB#s:
Series 1: 0, 34, 76
Series 2: 174, 188, 158
Series 3: 111, 150, 201
etc
Are these Embedded Excel charts or Office Charts (Microsoft Graph)?
Mostly PowerPoint MSGraph. Rarely, embedded Excel.

Are they linked to Excel Workbooks?
We tried to avoid linked Excel because of files being sent to clients, linked files are frequently forgotten.

Thanks for responding!

 

[SkipVought]

mblaster,

How's this?

Sub atest()
    For Each sl In ActivePresentation.Slides
        For Each sh In sl.Shapes
            If sh.Type = msoEmbeddedOLEObject Then
                i = 1
                For Each s In sh.OLEFormat.Object.SeriesCollection
                    Select Case i
                        Case 1
                            s.Interior.Color = RGB(0, 34, 76)
                        Case 2
                            s.Interior.Color = RGB(174, 188, 158)
                        Case 3
                            s.Interior.Color = RGB(111, 150, 201)
                    End Select
                    i = i + 1
                Next
            End If
        Next
    Next
End Sub

Hope it helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[mblaster]

Thank you! Your code worked perfectly for the Bar Charts. I added the additional "case #" for up to the 12 colors with their RGB#s.

But it stop for line/pie charts.

Do I need a different macro for each type of chart?
Do you know if there are different RGB standards? The colors for the first three that you added look great! But the additional case #s I added the colors look off. Any ideas?

 

[SkipVought]

m,

I've just beem fishing, using VB Help, the Object Browser and the Watches Window.

You should not have to make 3 separate procedures, if you test the ChartType for each chart.

The test that I am using for the 3DPie (3 series with 4 points) only one series at a time displays, so Color has to be applies to the Points. Have not got that yet.

Interior does not seem to apply to lines. Have not got that yet.

VB Help is not very comprehensive. I can only get help for some Chart Objects by using the Object Browser.

BTW, you need to add a reference to MS Graph in VB -- menu item Tools/References.

Here's the code WITH a test for the ChartType...

Sub atest()
    Dim sl As Slide, sh As Shape, ch As Chart, s As Series
    For Each sl In ActivePresentation.Slides
        For Each sh In sl.Shapes
            If sh.Type = msoEmbeddedOLEObject Then
                i = 1
                Set ch = sh.OLEFormat.Object
                Select Case ch.ChartType
                    Case xl3DPie
                    
                    Case xl3DBarClustered
                        For Each s In ch.SeriesCollection
                            Select Case i
                                Case 1
                                    s.Interior.Color = RGB(0, 34, 76)
                                Case 2
                                    s.Interior.Color = RGB(174, 188, 158)
                                Case 3
                                    s.Interior.Color = RGB(111, 150, 201)
                            End Select
                            i = i + 1
                        Next
                    Case xlLineMarkers
                    
                End Select
            End If
        Next
    Next
    Set ch = Nothing
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

I got the Pie Chart color to work. Still working on Line...

Sub atest()
    Dim sl As Slide, sh As Shape, ch As Chart, s As Series
    For Each sl In ActivePresentation.Slides
        For Each sh In sl.Shapes
            If sh.Type = msoEmbeddedOLEObject Then
                i = 1
                Set ch = sh.OLEFormat.Object
                Select Case ch.ChartType
                    Case xl3DPie
                        For Each s In ch.SeriesCollection
                            i = 1
                            For Each p In s.Points
                                Select Case i
                                    Case 1
                                        p.Interior.Color = RGB(0, 34, 76)
                                    Case 2
                                        p.Interior.Color = RGB(174, 188, 158)
                                    Case 3
                                        p.Interior.Color = RGB(111, 150, 201)
                                End Select
                                i = i + 1
                            Next
                        Next
                    Case xl3DBarClustered
                        For Each s In ch.SeriesCollection
                            Select Case i
                                Case 1
                                    s.Interior.Color = RGB(0, 34, 76)
                                Case 2
                                    s.Interior.Color = RGB(174, 188, 158)
                                Case 3
                                    s.Interior.Color = RGB(111, 150, 201)
                            End Select
                            i = i + 1
                        Next
                    Case xlLineMarkers

            End If
        Next
    Next
    Set ch = Nothing
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[TonyJollans]

Hi mblaster,

Skip is doing a grand job for you and I only have a little to add about colours. I think I can explain the reason for some of your colours looking off but what I can’t do is tell you how to fix it because, like Skip, I am finding the Help woefully inadequate.

MS Graph appears to share with MS Excel a limited capacity for colours. Hopefully a description of what happens in Excel will help. In Excel a Workbook can only display colours which are in its 56-colour palette. The default palette is what you see in, for example, the format dialogs. It contains 10 duplicates so there is some slack to add your own colours to it without losing any of the default ones.

When you assign a colour to an object by setting its Color Property to an RGB value, it is changed to the closest one to it in the colour palette. It doesn’t just display the colour from its palette, it actually changes the value you assign; you can see this by doing something like:

[a1].Interior.Color = 196
Msgbox [A1].Interior.Color

If you want to have your colour ‘properly’ displayed it must exist in the colour palette BEFORE you assign it. So, to display, for example colour 196, you need to do something like this:

ActiveWorkbook.Colors(30) = RGB(196, 0, 0)
[a1].Interior.Color = 196

Colour number 30 is the same as colour number 9 in the default palette so you won’t lose any other colours doing this. The other duplicates are 25, 26, 27, 28, 29, 31, 32, 34 and 54.

I am still trying to work out how to do the equivalent in Charts. Maybe somebody else knows.

Enjoy,
Tony

 

[mblaster]

Thanks Tony,
That does clear up some confusion about Excel. But I still have not found a way to directly change PowerPoint's Color Palette.

In Excel I used:
ActiveWorkbook.Colors(17) = RGB(153, 153, 255)

Skip is a Wizard!
After using his first macro the barcharts did change color but I have not gotten the other two to work yet. It keeps stopping in the DIM line "ch As Chart, s As Series". I must be doing something wrong. I copied and pasted but no luck.

Thanks again to both you and Skip!

 

[TonyJollans]

Hi mblaster,

Sounds like you need a Reference to Microsoft Graph.

Enjoy,
Tony