+ Reply to Thread
Results 1 to 13 of 13

How to apply cell/ colunm colours to the corresponding traces in the chart

  1. #1
    Registered User
    Join Date
    07-08-2019
    Location
    cambridge
    MS-Off Ver
    365
    Posts
    8

    How to apply cell/ colunm colours to the corresponding traces in the chart

    Hello everyone, I am slightly new to Excel (MS Office 365). I am trying to apply the cell or whole colunm font colour to the corresponding trace in the chart/graph. Please, could anyone help.



    2019-07-08 133244.jpg

  2. #2
    Valued Forum Contributor
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,620

    Re: How to apply cell/ colunm colours to the corresponding traces in the chart

    Hi
    if you have a standard line chart and are trying to get each line to have the same colour as the font in the cells contain that series' data, try this macro:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    07-08-2019
    Location
    cambridge
    MS-Off Ver
    365
    Posts
    8

    Re: How to apply cell/ colunm colours to the corresponding traces in the chart

    Hello NickyC. Thank you so much for the reply but it didn't work for me. I attached the screen shot. Attachment 631985

  4. #4
    Valued Forum Contributor
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,620

    Re: How to apply cell/ colunm colours to the corresponding traces in the chart

    Hi
    I can't read the screen shot but I don't think it would help. Can you upload a file with the charts or something similar (removing any personal/sensitive data first)
    Last edited by NickyC; 07-14-2019 at 01:53 AM.

  5. #5
    Registered User
    Join Date
    07-08-2019
    Location
    cambridge
    MS-Off Ver
    365
    Posts
    8

    Re: How to apply cell/ colunm colours to the corresponding traces in the chart

    Hi NickyC, Please have a look at the attached file. Many thanks
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,620

    Re: How to apply cell/ colunm colours to the corresponding traces in the chart

    Hi

    My apologies for not replying sooner, I was away last week.

    There is no chart in the file you attached but I think the problem may be that the macro is reading the colour of the first cell for the series, which contains the series title. This was coloured in your original screenshot but not in the attached workbook.

    If that the problem then this variation should work.

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    07-08-2019
    Location
    cambridge
    MS-Off Ver
    365
    Posts
    8

    Re: How to apply cell/ colunm colours to the corresponding traces in the chart

    I am so sorry. I didn't realise that I should add a chart for that table. I will try this macro and see what happens. I will let you know. Thanks a lot

  8. #8
    Registered User
    Join Date
    07-08-2019
    Location
    cambridge
    MS-Off Ver
    365
    Posts
    8

    Re: How to apply cell/ colunm colours to the corresponding traces in the chart

    I am sorry to let you know that, I don't know how to add your Macro to my workbook. My excel skills are very very basic.

  9. #9
    Valued Forum Contributor
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,620

    Re: How to apply cell/ colunm colours to the corresponding traces in the chart

    that's ok - this site exists to help people learn

    To copy and use a macro
    press alt+F11 to open the visual basic (VBA) area
    select insert > module from the menus
    copy the code from this website and paste it into the VBA module you just inserted
    Return to the spreadsheet. On the developer tab, select macros and click the name of the macro you want to run

    If you cannot see the developer tab
    Click the File tab
    Click Options
    Click Customize Ribbon
    Under Customize the Ribbon and under Main Tabs, select the Developer check box

    Macros can only be saved in a macro-enabled workbook, so if you will want to use the macro again you will need to select
    file > SaveAs
    and pick macro enabled workbook from the list of file types. This should save your file with an extension of .xlsm rather than the usual .xlsx

  10. #10
    Registered User
    Join Date
    07-08-2019
    Location
    cambridge
    MS-Off Ver
    365
    Posts
    8

    Re: How to apply cell/ colunm colours to the corresponding traces in the chart

    Dear NickyC, I am getting [ Run-time error '91': Object variable or with block variable not set ] and the third line in your macro becomes yellow with an arrow.

  11. #11
    Valued Forum Contributor
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,620

    Re: How to apply cell/ colunm colours to the corresponding traces in the chart

    Hi
    The macro works on a selected chart. If your charts are standard excel charts, try selecting the chart (or an element in it such as a series) and then running the macro. If that doesn't work, could you upload a workbook that includes a chart so I can see what might be going wrong? It may have to wait until tomorrow as I'll be logging off shortly.

  12. #12
    Registered User
    Join Date
    07-08-2019
    Location
    cambridge
    MS-Off Ver
    365
    Posts
    8

    Re: How to apply cell/ colunm colours to the corresponding traces in the chart

    Dear NickyC. I am so gratful. it worked. Selecting the graph made it work. Thank you so much.

  13. #13
    Registered User
    Join Date
    07-08-2019
    Location
    cambridge
    MS-Off Ver
    365
    Posts
    8

    Re: How to apply cell/ colunm colours to the corresponding traces in the chart


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1