Results 1 to 6 of 6

Thread: VBA That Colors Bars in a Bar Chart Based on DataPoint Name

  1. #1
    Neophyte bauer32's Avatar
    Join Date
    Jul 2019
    Posts
    3
    Articles
    0
    Excel Version
    Microsoft Excel 2010

    VBA That Colors Bars in a Bar Chart Based on DataPoint Name



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a bar chart that fluctuates depending on how many datapoint values I have. If there is a datapoint that doesn't have a value, I have a macro that hides that row with the blank values, which then condenses my graph. That way my graph can have 2 to 15 datapoint values. The issue I am having is I want datapoint#1 to be a gradient fill that has 3 stops (0%, 50%, 100%, with no angle) colored black, datapoint#2 to be orange, datapoint#3 to be green, and so on. If I pre-set the bar colors 1-15, and I don't have datapoint#2, then datapoint#3 will be colored with datapoint#2's color (orange instead of green). Wondering if there is a way to write a macro that will color the bars based on the datapoint name so the colors will remain stagnant no matter how many values I have in the graph. I have attached the spreadsheet as an example.Template.xlsmTemplate.xlsm

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,585
    Articles
    0
    Excel Version
    365
    This can get complicated.
    Did you mean not to plot row 45 (DataPoint#11, Yellow)?

  3. #3
    Neophyte bauer32's Avatar
    Join Date
    Jul 2019
    Posts
    3
    Articles
    0
    Excel Version
    Microsoft Excel 2010
    Quote Originally Posted by p45cal View Post
    This can get complicated.
    Did you mean not to plot row 45 (DataPoint#11, Yellow)?

    Thanks for responding! Basically, I need help writing something that will color the bars in the chart based on the Data Point name (Ex. If DataPoint#11 Then color gradient fill yellow, 0% & 100% stops (RGB) 97, 67, 0; 50% stop (RGB) 209, 144, 0). I found a video where someone put in code that randomly assigned colors to all of the bars but wondering if there is a way to assign colors based on which data points have values that are being shown in the graph and based on that data point name. Hope this makes sense. To answer your question, DataPoint#11 is plotted on the graph and is the last bar in the bar chart on the right. If you select the unhide button, the datapoint#11 fill goes from being yellow to a light green. Trying to make sure that the datapoint#11 bar always stays yellow, no matter it's position on the graph. Thanks!

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,585
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by bauer32 View Post
    To answer your question, DataPoint#11 is plotted on the graph and is the last bar in the bar chart on the right
    That's Adjusted Total on row 51. You have excluded DataPoint#11. I want to make sure that you have this right because to determine in VBA what ranges on a chart are plotted can be difficult, especially so in this case because you're already plotting non-contiguous ranges before you decide not to plot some points by hiding some of those rows.
    Click image for larger version. 

Name:	2019-07-25_160341.jpg 
Views:	8 
Size:	44.7 KB 
ID:	9269

    I need to determine what ranges are being plotted so that I can grab your colour from the same rows in column X
    Last edited by p45cal; 2019-07-25 at 04:10 PM.

  5. #5
    Neophyte bauer32's Avatar
    Join Date
    Jul 2019
    Posts
    3
    Articles
    0
    Excel Version
    Microsoft Excel 2010
    Quote Originally Posted by p45cal View Post
    No it's not, that's Adjusted Total on row 51. You have excluded DataPoint#11. I want to make sure that you have this right because to determine in VBA what ranges on a chart are plotted can be difficult, especially so in this case because you're already plotting non-contiguous ranges before you decide not to plot some points by hiding some of those rows.

    I'm very sorry, you are correct. Without my normal data labels I was looking at it incorrectly and also labeled it incorrectly. I did mean to exclude DataPoint#11, the Adjusted Total (row 51) should have been labeled as a datapoint. The Adjusted Total (row 51) should be colored yellow instead of DataPoint#11. Thanks!

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,585
    Articles
    0
    Excel Version
    365
    The attached is very much work-in-progress in that there are few checks made in the code so it can very easily go wrong. Hopefully your coding skills will add that aspect.
    I changed your colour table to be a single block (instead of two) and made each entry one row each.
    The code (Macro2) runs after every hide/unhide operation.
    Play with it, changing the colours in column X.
    It's fragile.
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

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