+ 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
    9

    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 2021 (Windows)
    Posts
    7,900

    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

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    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 2021 (Windows)
    Posts
    7,900

    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
    9

    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)

Similar Threads

  1. Conditional Formatting (colors) in Bubble Chart
    By SASO in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 12-24-2015, 12:25 PM
  2. Control bubble chart colors with conditional formatting in Excel 2010
    By jschmidtfpi in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-29-2013, 11:06 AM
  3. [SOLVED] Conditional Formatting Colors
    By FoxRacing1234 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-25-2013, 10:07 AM
  4. [SOLVED] Conditional format Chart :Colors.
    By JapanDave in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 03-28-2013, 05:48 PM
  5. Update line chart series colors
    By Apple Ling in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-02-2010, 05:10 AM
  6. Conditional Formatting Colors
    By dmyoungsal in forum Excel General
    Replies: 3
    Last Post: 07-15-2008, 05:38 PM
  7. conditional formatting (need 10 colors)
    By dmyoungsal in forum Excel General
    Replies: 7
    Last Post: 07-15-2008, 08:58 AM

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