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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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

261l24z.jpg


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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top