on PowerPoint Presentation, Change the each Bar color of Excel Linked Graphs as per their value

[brajcool]

Hello Friends,

I'm new to this forum and this is my first post.

I need help of you guys to solve a headache.

Currently I'm working on a project which require to change the Bar colors as per values of Excel linked graphs.

The problem is when the values changed in Excel sheet, values change on PPT graph also but the color of Bar doesn't change.

There are several slides and on every slide there is only one graph. Please refer attached PowerPoint Presentation

I have found one code on your forum, however as I'm new to PowerPoint VBA, not able to convert as per my use, however make some changes though but I stuck where need to set Chart:

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.Shape
If sh.Type = 3 Then
Set ch = sh.OLEFormat.Object 'here I stuck. Error Message "OLEFormat (unknown number) : Invalid Request. This property only applies to OLE Objects"
sh.Select
i = 1
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
End If
Next
Next
End Sub

Please help as this is very urgent project and I am very much stuck into it.

 

[SkipVought]

Hi,

First off I'd recommend getting familiar with the Watch Window. faq707-4594

Next....

For Each sh In sl.Shapes

A Shape is in The Shapes collection.

Next....

set ch = sh.Chart

I "discovered" that property of sh (Shape) on your sl (Slide), using the Watch Window!

Now I'm down to the series loop and I have a type mismatch on the series object I'm guessing. So I'm going to research with my Watch Window, what's going on. I'll probably change the s variable from Series to an Object and observe.

So while I'm digging, you might do so as well on your end, and let's figgure this out!

 

[brajcool]

Thanks Skip for prompt response.

I tried to use watch window by using the above link in your other posts but I couldn't.

Surely I am trying to use it right now.

 

[SkipVought]

Okay, changed the s variable to Object and it runs to completion.

You have TWO series: Fundraising Totals and Average.

So how do you want the Fundraising Totals shaded?

 

[brajcool]

Hey Skip,

I tried to run the code by changing the s variable as object but it's not working.

May I request you to share the whole code so that would be clear how you have declared the variables.

As far as color are concern for Series, actually every bar is need to be colored as per data point value.

I know it's not good but don't have idea regarding the RGB color of those specific colors. Please let me know that how can I get the RGB color code of specific colors in excel.

Thanks a lot for your help.

Braj

 

[SkipVought]

I still don't understand how you wan to color your series columns (Points) using RGB. YOUR values are between 0 and 1! ???? We need a better proposal.

This code runs.

Sub atest()
    Dim sl As Slide, sh As Shape, ch As Chart, s As Object

    For Each sl In ActivePresentation.Slides
        For Each sh In sl.Shapes
            If sh.Type = 3 Then
            Set ch = sh.Chart
                i = 1
                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
            End If
        Next
    Next
End Sub

 

[brajcool]

Hello Skip,

I used the same code as you have shared now, however code is not running due to error on this error:

Set ch = sh.Chart 'Complie Error: Method or data member not found

Also for condition of the code, I have attached the sample excel file on which Range "E36:G39" has the conditions and colors.

Thanks,
Braj

 

[brajcool]

Sorry,

Please find the link for excel file.

 

[SkipVought]

Your Excel sheet/chart has millions

Your PP charts have PERCENTAGES!!!

Apples and oranges!!!

come on, please get your stuff together!

 

[brajcool]

Yes I know that but that is how I got this project actually.

Kindly set your code as per the percentages which have been mentioned next to the color in attached excel.

Then I would have got an idea that how to do it and after that will discuss with my client how he want it, right now he wanted it as looking in PPT.

I know it's little bit confusing but please help to sort it out.

Thank you once again for continues support on this.

 

[SkipVought]

You just threw a lot of data in that sheet. How does that relate to your chart??

The condition is determined by the value of cell [highlight #FCE94F]C31[/highlight] for the first column
each column value is determined by the value of [highlight #FCE94F]row 31[/highlight]

If value in [highlight #FCE94F]row 31[/highlight] is

[highlight #FCE94F]Well row 31 has NOTHING in it![/highlight]

As Ricky would say to Lucy, "You got a lot of 'splainin to do!"

Why can't you color the columns in Excel?

 

[brajcool]

Hello Skip,

You are my Man Bro!!!

Exactly the same reaction of mine also but sometimes even every time BOSS is right Dude.

I said that we can do condition formatting in Excel and then transfer the graph to PowerPoint but as per him then there would be no sense to link the graph with excel sheet and strictly instructed me sort out this issue in Power Point itself.

So my friend I have to do it, come what may. After that I can make him know the difficulties and irregularity of information.

For the time being do it as per the Data Point value in PowerPoint Graph, if possible.

Thank you very much.

 

[SkipVought]

Okay, regardless of where the coloring is accomplished, the charts end up in PP???

Well the real issue is how to translate chart series point values between 0 and 1 to meaningful colors.

For some reason, your PP PERCENTAGE charts refer to MILLIONS OF DOLLAR COLOR CODED RANGES??? Do you have an answer for that?

And actually you have one chart where the range is 0 to 2% while others are 0 to 80%, and others between: a real challenge when it come to color differentiation!

Any ideas on the requirements side? Once we get a firm logical requirement, we can begin designing a solution. I already have a sample of shades based on point value, but the differentiation is minimal.

Ball's in your court!

 

[brajcool]

Okay, regardless of where the coloring is accomplished, the charts end up in PP??? - Yes

For some reason, your PP PERCENTAGE charts refer to MILLIONS OF DOLLAR COLOR CODED RANGES??? Do you have an answer for that? - Will get back to you

Thanks to raise these issues, will get back to you shortly with logical requirements.

Once again appreciate your time & efforts on this.

 

[SkipVought]

Okay, here's a shot at shades of red/blue. This approch loops thru the chart TWICE; first time to get the MIN & MAX values and the second time to spread the values completely across 0 - 255, the max value in RGB to get the greatest color differentiation, I think.

Sub atest()
    Dim sl As Slide, sh As Shape, ch As Chart, s As Object
    Dim pt As Object, nVal As Double, i As Integer, nMIN, nMAX

    For Each sl In ActivePresentation.Slides
        For Each sh In sl.Shapes
            If sh.Type = 3 Then
                Set ch = sh.Chart
                With ch.SeriesCollection(1)
                    nMAX = 0
                    nMIN = 100
                    For i = 1 To 2
                        For Each pt In .Points
                            With pt
                                nVal = Left(.DataLabel.Text, Len(.DataLabel.Text) - 1)
                                Select Case i
                                    Case 1  'get min & max point range
                                        If nMAX < nVal Then nMAX = nVal
                                        If nMIN > nVal Then nMIN = nVal
                                    Case 2  'assign colors spread across point range
                                        nVal = (nVal - nMIN) * 255 / nMAX
                                        .Interior.Color = RGB(nVal, 0, 255)  '
                                End Select
                            End With
                        Next
                    Next
                End With
            End If
        Next
    Next
End Sub

 

[brajcool]

Yeah Skip, the code is working. Thanks a lot!!

However the colors are different from the requirements, is there anything by which we get the exact colors or please let me know the way to find out the RGB format of specific color.

Thank you very much. You are a Gem buddy!!

 

[SkipVought]

I still do not know where your colors are that relate to the percentages???

 

[SkipVought]

Okay I see them in the NEW copy of your workbook.

I tried using ColorIndex last night to no avail. Today I'm trying Color in a lookup.

.....

 

[SkipVought]

Okay, I got it to work.

FIRST, your Color Table. It it in the Attached file below
[pre]
' Limits Cindex
[highlight #AD7FA8] [/highlight]0 9420794
[highlight #75507B] [/highlight]0.101 13082801
[highlight #729FCF] [/highlight]0.3 13995347
[highlight #8AE234] [/highlight]0.501 10213316
[/pre]

Limits & CIndex is a named range, used in PP VBA to lookup the CIndex Color value for any Percentage base value. PP code wants the Excel workbook to be open.

Sub atestB()
    Dim sl As Slide, sh As Shape, ch As Chart, s As Object
    Dim pt As Object, nVal As Double
    Dim xl As Excel.Application
    
    Set xl = GetObject(, "Excel.Application")

    For Each sl In ActivePresentation.Slides
        For Each sh In sl.Shapes
            If sh.Type = 3 Then
                Set ch = sh.Chart
                With ch.SeriesCollection(1)
                    For Each pt In .Points
                        With pt
                            nVal = Left(.DataLabel.Text, Len(.DataLabel.Text) - 1) / 100
                            .Interior.Color = xl.Index(xl.[CIndex], xl.Match(nVal, xl.Range("limits"), 1), 1)
                        End With
                    Next
                End With
            End If
        Next
    Next
End Sub

Hope this works for you.

 

[SkipVought]

Reiterating my objection to doing the coloring in PP rather than Excel, people could access the workbook where the chart resides and....

...chart column colors are absent. DISCONNECT!!!

The SOURCE is the place to accomplish this for this reason AND the fact that it is easier accomplished. The chart legend already has these colors for what reason, as I pointed out earlier!

IMNSHO, it makes little sense, my pardon to your boss, to color the points this way!