Automatic color selection for selected chart labels

firstmemories

New Member
Joined
Apr 16, 2008
Messages
4
I create stacked column and pie charts to represent market share for various companies, in specific industries. I do this every quarter or every year with new data for the same set of companies. The chart data series for each part of the column, or slice of the pie, is a specific color for each company. (For example, all pie chart slices for IBM might need to be RGB 0,0,102.)

Is there a way for me to set up my charts or source data so that "IBM" is always automatically set to RGB 0,0,102, "Microsoft" is always RGB X,Y,Z, etc?

Thank you
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

steve case

Well-known Member
Joined
Apr 10, 2002
Messages
815
I'm doing a search on this topic of "How can I select my color instead of the default automatic color selection?" I ran across this post which has no reply. So I'm guessing that you can't. I'm guessing that when you go to Source Data click on Add Series you have no choice but Patterns Automatic Line Color. If that's the case, and I'm guessing correctly then it would be great if an expert in Excel would verify that such is the case, and I can stop wasting time looking elsewhere for the answer.

Here's my post on the topic

Here's what I'm doing



I need all the series to be one color, and going back to format all 29 lines so they are black gets to be tedious.

A macro that says, "Select (Chart1) Select (All Series) Select (Color) Select (Black) Select (OK)" would be just the ticket.
 

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,453
steve case,

This will turn all lines in a line chart to black....

Code:
[COLOR=#0000cd]Sub [/COLOR]Test()

[COLOR=#0000cd]Dim [/COLOR]IntLp [COLOR=#0000cd]As[/COLOR] [COLOR=#0000cd]Integer[/COLOR]
[COLOR=#0000cd]Dim [/COLOR]SeriesHigh [COLOR=#0000cd]As[/COLOR][COLOR=#0000cd] Integer[/COLOR]
[COLOR=#0000cd]Dim[/COLOR] ser [COLOR=#0000cd]As[/COLOR] Series
[COLOR=#0000cd]Dim [/COLOR]cht [COLOR=#0000cd]As[/COLOR] ChartObject

[COLOR=#0000cd]Set[/COLOR] cht = Worksheets("Sheet1").ChartObjects("Chart 1")
SeriesHigh = cht.Chart.SeriesCollection.Count

[COLOR=#0000cd]For[/COLOR] IntLp = 1 [COLOR=#0000cd]To[/COLOR] SeriesHigh
   [COLOR=#0000cd] Set[/COLOR] ser = cht.Chart.SeriesCollection(IntLp)
    ser.Format.Line.Visible =[COLOR=#000000] msoFalse
    ser.Format.Line.Visible = msoTrue[/COLOR]
    cht.Chart.SeriesCollection(IntLp).Format.Line.ForeColor.RGB = RGB(1, 1, 1)
[COLOR=#0000cd]Next[/COLOR] IntLp

[COLOR=#0000cd]End Sub[/COLOR]
 
Last edited:

steve case

Well-known Member
Joined
Apr 10, 2002
Messages
815
steve case,

This will turn all lines in a line chart to black....

Code:
[COLOR=#0000cd]Sub [/COLOR]Test()

[COLOR=#0000cd]Dim [/COLOR]IntLp [COLOR=#0000cd]As[/COLOR] [COLOR=#0000cd]Integer[/COLOR]
[COLOR=#0000cd]Dim [/COLOR]SeriesHigh [COLOR=#0000cd]As[/COLOR][COLOR=#0000cd] Integer[/COLOR]
[COLOR=#0000cd]Dim[/COLOR] ser [COLOR=#0000cd]As[/COLOR] Series
[COLOR=#0000cd]Dim [/COLOR]cht [COLOR=#0000cd]As[/COLOR] ChartObject

[COLOR=#0000cd]Set[/COLOR] cht = Worksheets("Sheet1").ChartObjects("Chart 1")
SeriesHigh = cht.Chart.SeriesCollection.Count

[COLOR=#0000cd]For[/COLOR] IntLp = 1 [COLOR=#0000cd]To[/COLOR] SeriesHigh
   [COLOR=#0000cd] Set[/COLOR] ser = cht.Chart.SeriesCollection(IntLp)
    ser.Format.Line.Visible =[COLOR=#000000] msoFalse
    ser.Format.Line.Visible = msoTrue[/COLOR]
    cht.Chart.SeriesCollection(IntLp).Format.Line.ForeColor.RGB = RGB(1, 1, 1)
[COLOR=#0000cd]Next[/COLOR] IntLp

[COLOR=#0000cd]End Sub[/COLOR]
Thanks for that, I will be trying that out tomorrow, and I will post here how that goes. Hopefully, that will be a great big Thank You Thank You Thank You.
 

steve case

Well-known Member
Joined
Apr 10, 2002
Messages
815
steve case,

This will turn all lines in a line chart to black....

Code:
[COLOR=#0000cd]Sub [/COLOR]Test()

[COLOR=#0000cd]Dim [/COLOR]IntLp [COLOR=#0000cd]As[/COLOR] [COLOR=#0000cd]Integer[/COLOR]
[COLOR=#0000cd]Dim [/COLOR]SeriesHigh [COLOR=#0000cd]As[/COLOR][COLOR=#0000cd] Integer[/COLOR]
[COLOR=#0000cd]Dim[/COLOR] ser [COLOR=#0000cd]As[/COLOR] Series
[COLOR=#0000cd]Dim [/COLOR]cht [COLOR=#0000cd]As[/COLOR] ChartObject

[COLOR=#0000cd]Set[/COLOR] cht = Worksheets("Sheet1").ChartObjects("Chart 1")
SeriesHigh = cht.Chart.SeriesCollection.Count

[COLOR=#0000cd]For[/COLOR] IntLp = 1 [COLOR=#0000cd]To[/COLOR] SeriesHigh
   [COLOR=#0000cd] Set[/COLOR] ser = cht.Chart.SeriesCollection(IntLp)
    ser.Format.Line.Visible =[COLOR=#000000] msoFalse
    ser.Format.Line.Visible = msoTrue[/COLOR]
    cht.Chart.SeriesCollection(IntLp).Format.Line.ForeColor.RGB = RGB(1, 1, 1)
[COLOR=#0000cd]Next[/COLOR] IntLp

[COLOR=#0000cd]End Sub[/COLOR]

Crashed, a dialog box comes up that says:

Run-time error '1004':


Unable to get the ChartObjects property of the Worksheet class
Selecting [Debug] highlights this line:

Code:
[COLOR=#0000cd]Set[/COLOR] cht = Worksheets("Sheet1").ChartObjects("Chart 1")
I have Excel 2003 if that's the issue.
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,851
Office Version
365
Platform
Windows, MacOS
Select the first series, format the color, click the up arrow to select the second series, press F4 key to repeat. Cycle up arrow and F4 key until you run out of series.

If you need to change the line width, do the color first and arrow-F4 your way through, then do the line width and repeat the arrow-F4 cycle.

You can't change the marker this way; F4 doesn't recognize a lot of chart formatting steps.
 

steve case

Well-known Member
Joined
Apr 10, 2002
Messages
815
Select the first series, format the color, click the up arrow to select the second series, press F4 key to repeat. Cycle up arrow and F4 key until you run out of series ...
Thank you Thank You T H A N K Y O U ! !

Took me a while to figure out what was going on but then yes, it worked like a champ (-:

I can resume my little project now knowing that a good deal of the tedium has vanished.
 

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,453
Jon,

Thanks for the information. Didn't know F4 was applicable for charts. I visit your site often. Thanks for all of the content you've produced over the years. Just read your blog post about the Excel Summit South. Sounded like a lot of fun.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,803
Messages
5,446,560
Members
405,407
Latest member
apat

This Week's Hot Topics

Top