Automatic color selection for selected chart labels

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Automatic color selection for selected chart labels

  1. #1
    New Member
    Join Date
    Apr 2008
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Automatic color selection for selected chart labels

     
    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

  2. #2
    Board Regular steve case's Avatar
    Join Date
    Apr 2002
    Posts
    761
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatic color selection for selected chart labels

    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.
    AKA StACase

  3. #3
    Board Regular mrmmickle1's Avatar
    Join Date
    May 2012
    Location
    Charlotte, NC
    Posts
    2,444
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatic color selection for selected chart labels

    steve case,

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

    Code:
    Sub Test()
    
    Dim IntLp As Integer
    Dim SeriesHigh As Integer
    Dim ser As Series
    Dim cht As ChartObject
    
    Set cht = Worksheets("Sheet1").ChartObjects("Chart 1")
    SeriesHigh = cht.Chart.SeriesCollection.Count
    
    For IntLp = 1 To SeriesHigh
        Set ser = cht.Chart.SeriesCollection(IntLp)
        ser.Format.Line.Visible = msoFalse
        ser.Format.Line.Visible = msoTrue
        cht.Chart.SeriesCollection(IntLp).Format.Line.ForeColor.RGB = RGB(1, 1, 1)
    Next IntLp
    
    End Sub
    Last edited by mrmmickle1; Apr 5th, 2016 at 09:01 PM.
    Matt Mickle
    Microsoft MVP - Excel
    Using Excel & Access 2010, 2013 & 2016 | Windows 7 | 64 Bit

    Post a screen shot with the MrExcel HTML Maker Add-In or the Excel Jeanie Add-In

  4. #4
    Board Regular
    Join Date
    Jan 2008
    Posts
    814
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatic color selection for selected chart labels

    Try this, I was trying to figure this out as well the other day. Although I didn't have time to read through.

    Conditional Formatting of Excel Charts - Peltier Tech Blog

    Great site for all your charting needs.

  5. #5
    Board Regular steve case's Avatar
    Join Date
    Apr 2002
    Posts
    761
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatic color selection for selected chart labels

    Quote Originally Posted by mrmmickle1 View Post
    steve case,

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

    Code:
    Sub Test()
    
    Dim IntLp As Integer
    Dim SeriesHigh As Integer
    Dim ser As Series
    Dim cht As ChartObject
    
    Set cht = Worksheets("Sheet1").ChartObjects("Chart 1")
    SeriesHigh = cht.Chart.SeriesCollection.Count
    
    For IntLp = 1 To SeriesHigh
        Set ser = cht.Chart.SeriesCollection(IntLp)
        ser.Format.Line.Visible = msoFalse
        ser.Format.Line.Visible = msoTrue
        cht.Chart.SeriesCollection(IntLp).Format.Line.ForeColor.RGB = RGB(1, 1, 1)
    Next IntLp
    
    End Sub
    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.
    AKA StACase

  6. #6
    Board Regular steve case's Avatar
    Join Date
    Apr 2002
    Posts
    761
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatic color selection for selected chart labels

    Quote Originally Posted by mrmmickle1 View Post
    steve case,

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

    Code:
    Sub Test()
    
    Dim IntLp As Integer
    Dim SeriesHigh As Integer
    Dim ser As Series
    Dim cht As ChartObject
    
    Set cht = Worksheets("Sheet1").ChartObjects("Chart 1")
    SeriesHigh = cht.Chart.SeriesCollection.Count
    
    For IntLp = 1 To SeriesHigh
        Set ser = cht.Chart.SeriesCollection(IntLp)
        ser.Format.Line.Visible = msoFalse
        ser.Format.Line.Visible = msoTrue
        cht.Chart.SeriesCollection(IntLp).Format.Line.ForeColor.RGB = RGB(1, 1, 1)
    Next IntLp
    
    End Sub

    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:
    Set cht = Worksheets("Sheet1").ChartObjects("Chart 1")
    I have Excel 2003 if that's the issue.
    AKA StACase

  7. #7
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,400
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatic color selection for selected chart labels

    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.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  8. #8
    Board Regular steve case's Avatar
    Join Date
    Apr 2002
    Posts
    761
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatic color selection for selected chart labels

    Quote Originally Posted by Jon Peltier View Post
    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.
    AKA StACase

  9. #9
    Board Regular mrmmickle1's Avatar
    Join Date
    May 2012
    Location
    Charlotte, NC
    Posts
    2,444
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatic color selection for selected chart labels

      
    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.
    Matt Mickle
    Microsoft MVP - Excel
    Using Excel & Access 2010, 2013 & 2016 | Windows 7 | 64 Bit

    Post a screen shot with the MrExcel HTML Maker Add-In or the Excel Jeanie Add-In

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com