+ Reply to Thread
Results 1 to 9 of 9

Pivot table graph colour

  1. #1
    Registered User
    Join Date
    04-18-2013
    Location
    bradford
    MS-Off Ver
    Excel 2010
    Posts
    92

    Pivot table graph colour

    Hi

    This is probably a very basic question but i am just starting with pivot tables and was wondering how i do the following:-

    In the attached i am trying to show jans data for three years all in the same colour, then febs in another colour etc so that each site can see the trends in each month for each year, i can change these but they refer back to random colours.

    if i deselect jan 2015 it removes it from the graph but if i re select it it does nor re appear, even if i refresh the data.

    am i just been an idiot here?

    Regards

    Peter
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Pivot table graph colour

    You have to apply the colors after the fact.

    The following has code (Macro ChartColor) that reads the Color Map. The color map has the name of the series in Column A. These names must match the pivot table columns that I note are inconsistent. Column B has the color index of the shade of the cell in column A. Fill the cell with the color you want, run the macro and the program will take care of it from there.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    04-18-2013
    Location
    bradford
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Pivot table graph colour

    Hi dflak

    Thank you very much for looking at this for me, i have tidied up the headers in the table and in the color list
    but when i run the macro it comes up with the error ( method data not found ) What do i need to rectify this?
    I have been trying to look at the code Do i need to re name the data?


    Regards


    Peter
    Attached Images Attached Images
    Attached Files Attached Files

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Pivot table graph colour

    Hi,

    FullSeriesCollection does not exist in Excel 2010. Replace it with SeriesCollection.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  5. #5
    Registered User
    Join Date
    04-18-2013
    Location
    bradford
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Pivot table graph colour

    Hi

    Thank you both for your help,
    Removing the word full worked

    For some reason it would not update the date in the pivot table so i had to delete the pivot table and add a new one and change the chart name in the code :-)


    Is there any way that the color code macro could but a black border to define the data graphs?


    regards

    Peter
    Attached Images Attached Images
    Attached Files Attached Files

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Pivot table graph colour

    I think you can set those manually and they will "stick." The code does nothing to change the outline.

  7. #7
    Registered User
    Join Date
    04-18-2013
    Location
    bradford
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Pivot table graph colour

    Hi

    The border lines disappear when i use the code.. i guess i will just have to re apply each time i change the data

    Thank you all for your help

    Peter

  8. #8
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Pivot table graph colour

    This version will add a black border
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    04-18-2013
    Location
    bradford
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Pivot table graph colour

    Hi xlnitwit

    Thank you very much, it works spot on

    Cheers

    Peter

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 12-31-2016, 04:06 AM
  2. Replies: 1
    Last Post: 07-29-2016, 04:30 PM
  3. [SOLVED] Amend Font Colourand Row Colour in a pivot table row
    By ZuneidDassu in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-04-2014, 06:09 AM
  4. Replies: 2
    Last Post: 08-29-2011, 12:29 PM
  5. Pivot table text colour
    By Mearsy in forum Excel General
    Replies: 1
    Last Post: 04-22-2008, 01:26 PM
  6. [SOLVED] Graph with pivot table
    By Richard Vajsar in forum Excel General
    Replies: 1
    Last Post: 07-18-2005, 07:05 PM
  7. [SOLVED] Pivot table - colour of headers
    By eeelllaaa in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-12-2005, 07:06 PM

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