+ Reply to Thread
Results 1 to 5 of 5

Colors don't update in chart with conditional formatting

  1. #1
    Registered User
    Join Date
    08-28-2019
    Location
    Montreal
    MS-Off Ver
    2013
    Posts
    7

    Colors don't update in chart with conditional formatting

    Hello

    I have found a VBA script on the WEB that colors a chart based on the "fill" color of the data. Sorry, I don't remember the original author of the VBA so I can't contact him directly.

    The script works like a charm... only ... it does not work when you have conditional formatting.

    Attached, I have a spreadsheet which highlights the problem.

    There are two colored tables and two graphs based on each of the tables. The tables look the same but the second table contains conditional formatting based on the value of C19 through C22.

    I have created a button that executes the macro. The top graph works like a charm but the graph based on the conditional formatted table does not have the right colors. In fact, they are all "NO fill" colors which are invisible.

    Is there a way to modify the VBA macro so that the colors are updated in the graph based on conditional formatted tables ?

    Thanks

    Canadane
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    5,337

    Re: Colors don't update in chart with conditional formatting

    Hi, the misunderstanding here is that conditional formatting does NOT actually color the cell, but just shows you a color you select based upon the rule you created in the conditional format.
    It's the same as a date value, you can show it as November 28, 2019 but the cell's actual value is 28/11/2019 (which you can see in the formula bar).
    A number format, a conditional format etc are what you see is what you want to see but not the what you see is what you have.
    Hope my crooked explanation makes sense
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you ( It doesn't hurt )

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    2003, 2007, 2010 & 2013
    Posts
    18,690

    Re: Colors don't update in chart with conditional formatting

    This slight mod to the way you gather the required colour will take into account CF

    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    5,337

    Re: Colors don't update in chart with conditional formatting

    @Andy Pope: Thanks for the lesson, learnt one more thing today

  5. #5
    Registered User
    Join Date
    08-28-2019
    Location
    Montreal
    MS-Off Ver
    2013
    Posts
    7

    Re: Colors don't update in chart with conditional formatting

    Thanks! Works like a charm
    Problem Solved.

    Canadane

+ 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