+ Reply to Thread
Results 1 to 23 of 23

pivot table colour change per filter

  1. #1
    Forum Contributor
    Join Date
    07-07-2019
    Location
    london
    MS-Off Ver
    2013
    Posts
    102

    pivot table colour change per filter

    Hi,

    much appreciate you help with this.

    can i assign a background colour to my whole pivot table, with that colour changing upon different selections of my filter. so i have a pivot table that filters by price. i wonder whether the whole background colour of the whole pivot table can change . i have 3 price filters of 0-5, 6-10, and 10+.

    many thanks for your help on this.

    i have google but the results are not quite what i was asking.

    thanks

    steve

  2. #2
    Forum Contributor
    Join Date
    07-07-2019
    Location
    london
    MS-Off Ver
    2013
    Posts
    102

    Re: pivot table colour change per filter

    bump bump.

    no one have any ideas on this ??

    i thought this May be straght forward to do, but not from the explanations on google !!, suggesting programming formulae into it? is there not a solution ??

    thanks

    steve

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: pivot table colour change per filter

    It sounds like code would be the best option for this. Can you post a sample workbook so we can see what we're dealing with?
    Rory

  4. #4
    Forum Contributor
    Join Date
    07-07-2019
    Location
    london
    MS-Off Ver
    2013
    Posts
    102

    Re: pivot table colour change per filter

    Hey Rorya, thanks for reply - just noticed it.

    i can only provide an example of the data. but i have cut the document, and attached.

    the source tab is the data.
    the actions tab is my table - of which - the key area is the "price" filter at the top. my question is can i change the colour of the whole table, to change colours upon each selection of price. so , eg, norm = yellow, high = red, low = blue.

    that would be fantastic to achieve but i cant seem to get that info anywhere.

    your help is greatly appreciated.

    regards steve

  5. #5
    Forum Contributor
    Join Date
    07-07-2019
    Location
    london
    MS-Off Ver
    2013
    Posts
    102

    Re: pivot table colour change per filter

    example attached
    Attached Files Attached Files

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: pivot table colour change per filter

    Right-click the worksheet tab, choose View Code and then paste this into the window that appears:

    Please Login or Register  to view this content.
    You'll then need to save the workbook in a macro-enabled format.

  7. #7
    Forum Contributor
    Join Date
    07-07-2019
    Location
    london
    MS-Off Ver
    2013
    Posts
    102

    Re: pivot table colour change per filter

    thanks v much rorya,

    it works on the attached example, however when i paste it into my main worksheet ( the actual file ) , the backgrounds go to a black colour.

    i didnt realise it required code, i thought it would be a selection from the tools somehow,.

    in hindsight i suppose i should have had just the border of the pivot table in different colours as the whole background is a little garish. is there a colour chart for code items ??, i assume im asking too much, as said didnt realize it was code i thought id be able to play around with the design a bit. many thanks anyway as what you have done has worked. i just need to tweak it, thanks

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: pivot table colour change per filter

    There was an error in what I posted before. This line:

    Please Login or Register  to view this content.
    should read:

    Please Login or Register  to view this content.
    Re the colours, you can use RGB values rather than literal constants - eg RGB(255,0,0) is the same as vbRed. If you want just borders, what border effect do you want? A border around the table, or each cell? It might be easier to create three pivot table styles with the colours you want and then apply them in the code. That way, if you want to alter the formatting, you can just amend the styles, not the code.

  9. #9
    Forum Contributor
    Join Date
    07-07-2019
    Location
    london
    MS-Off Ver
    2013
    Posts
    102

    Re: pivot table colour change per filter

    Hi, rorya,
    thanks for your help on this.
    i think it would definitely be better to just have a different colour border around the whole table. i would suggest a green border for the "norm" filter. a red border of the "high" filter and a yellow border for the "low" filter. and a grey border if no filter selected ( not sure if this last one is possible )

    i have tried to change your code to manually reflect the changes of colours. but i must have made a mistake as now the whole table has turned black.

    i would thoroughly appreciate if you could send the code like you did above. but with the colours in the border instead of the whole text.

    hope this is possible, with many thanks

    steve

  10. #10
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: pivot table colour change per filter

    Try something like this:

    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    07-07-2019
    Location
    london
    MS-Off Ver
    2013
    Posts
    102

    Re: pivot table colour change per filter

    WOW. thanks v much rorya. this works perfectly !!.

    i have 4 pivot tables on the same worksheet. all with data that comes from the same source. if i filter one table with NORM it goes green border - great. but if i select another table and filter it, the new table selected shows the correct filter and border colour. but the other table that i previously had a green border now looses its border completely.
    its as if the code can only work on one table at a time - but i would like it to work on all pivot tables.
    is there a way i can do this ?
    perhaps i can assign each pivot table a name
    and assign that name to 4 different sets of codes

    do you know if this is possible ??, thanks very much for all you have done.

    steve

  12. #12
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: pivot table colour change per filter

    Try this:

    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    07-07-2019
    Location
    london
    MS-Off Ver
    2013
    Posts
    102

    Re: pivot table colour change per filter

    i also have a one slicer that is linked to ALL tables. when i select a filter. it only applies to the border on ONE of the tables. this is kindof linked to my reply just now. not sure if its relevent as im sure if i ( you !!! ) solve my question above i think this will also solve as the slicer is just applying the filter.

  14. #14
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: pivot table colour change per filter

    Did you try my last code?

  15. #15
    Forum Contributor
    Join Date
    07-07-2019
    Location
    london
    MS-Off Ver
    2013
    Posts
    102

    Re: pivot table colour change per filter

    ok. your an excel wizard. that has worked. very well done indeed !!!!!

  16. #16
    Forum Contributor
    Join Date
    07-07-2019
    Location
    london
    MS-Off Ver
    2013
    Posts
    102

    Re: pivot table colour change per filter

    ok. i am amazed that there is one more thing now not working. EVEN after you solved it. its such a small thing, but it will occur everytime and is quite annoying in that it stops your code working ( considering you just achieved the result !! )

    i select my filters via a slicer.

    if i select MORE THAN ONE filter on the slicer. by holding down the ctrl and selecting two filters. then everything works ok, the coloured background changes to black ( obviously the code doesnt assign a colour as no particular filter has been set )
    however the problem occurs when i go back to choosing just one filter, the slicer selects it ok and the filter works, but still considers it to be in "select multiple items" mode. with that box being ticked. (even though only one filter is selected)
    and this has the problem of the code NOT assigning a background colour again.

    i had no idea it would be this detailed to get it working again as i need to go into price and deselect the "select multiples box"

    so i suppose my question is
    A. can i get the slicer to "untick" the select multiple items tickbox, when i go back to just one filter
    OR
    B. can the code pick up on the fact that only one filter is chosen BUT when "select multiple items" is ticked. the code still works
    i suppose either one would get the desired results.

    would really appreciate and interested in your views on this .

    thanks

    steve

  17. #17
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: pivot table colour change per filter

    I'm confused - the code you have now doesn't colour any backgrounds.

  18. #18
    Forum Contributor
    Join Date
    07-07-2019
    Location
    london
    MS-Off Ver
    2013
    Posts
    102

    Re: pivot table colour change per filter

    i mean border, not background

  19. #19
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: pivot table colour change per filter

    OK. Anything else I need to know before I start looking at this?

  20. #20
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: pivot table colour change per filter

    Also, what is the name of the slicer?

  21. #21
    Forum Contributor
    Join Date
    07-07-2019
    Location
    london
    MS-Off Ver
    2013
    Posts
    102

    Re: pivot table colour change per filter

    ok. no, i have looked around web but cant find any solutions, just hope my post makes sense.

    its basically, when the "select multiple items" box is ticked. the border colour goes black - this is fine i can live with this.
    the problem is when i revert to just a single filter , using the slicer , the filter works and the data shows just that single filter BUT the "select multiple items" box is still ticked, and therfor code doesnt activate.

    only way is for me to deselect the tick on the "select multiple items" box on each of my 5 pivot tables. even though they are all connected.

    hope you can shed light on this problem or better still find a solution !!!

    thanks all for your help.

  22. #22
    Forum Contributor
    Join Date
    07-07-2019
    Location
    london
    MS-Off Ver
    2013
    Posts
    102

    Re: pivot table colour change per filter

    slicer name is Price

  23. #23
    Forum Contributor
    Join Date
    07-07-2019
    Location
    london
    MS-Off Ver
    2013
    Posts
    102

    Re: pivot table colour change per filter

    i have read a post on here that disables the multi-select function of a slicer. however i want to keep the function as i would use it often. thats the problem really , once its used the slicer doesnt revert back (and untick) the select multiple items box.

    its strange because reading your code, from a layman, its saying if the filter is NORM then the border is vbGreen.

    and my price filter IS norm. its just that the select multiple items is ticked !!

    ??????? confused

+ 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. Change filter pivot table
    By fiqarcobel in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-06-2019, 11:59 AM
  2. Change Chart Pivot series Colour depending from Pivot Table Filter value
    By BrianAll in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-30-2018, 02:20 PM
  3. Change on Pivot Table Filter
    By BrianAll in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-20-2017, 02:10 PM
  4. Replies: 6
    Last Post: 07-31-2014, 12:56 PM
  5. Replies: 3
    Last Post: 11-27-2013, 05:25 PM
  6. Pivot table, assigning a cell wo be change the Pivot table filter
    By jwongsf in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-28-2012, 05:00 PM
  7. Change Pivot table Filter Based on Cell Value *Multiple Filter items* Possible?
    By Flydd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-27-2012, 06:57 AM

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