+ Reply to Thread
Results 1 to 12 of 12

Creating full-spectrum color gradients in bar chart based on input value

  1. #1
    Registered User
    Join Date
    12-12-2020
    Location
    Spokane, WA
    MS-Off Ver
    2016
    Posts
    6

    Creating full-spectrum color gradients in bar chart based on input value

    I'm creating a resource for the stats behind the mechanics of a video game I'm designing and would like the value of each stat to scale from red (lowest) through yellow, to green, and finally cyan (highest). I'd post an image link but I'm not allowed to yet; the stat bars for the Pokemon on the Smogon site is very similar to what I'm going for.

    Ideally the color and length of the bar would automate once a number is input. In Excel the fill effects only allow for two colors rather than a gradual spectrum; any help on how to implement this is much appreciated.

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

    Re: Creating full-spectrum color gradients in bar chart based on input value

    You can apply a gradient fill the bar. In the Format dialog you can add Graident stops and set the colours
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    12-12-2020
    Location
    Spokane, WA
    MS-Off Ver
    2016
    Posts
    6

    Re: Creating full-spectrum color gradients in bar chart based on input value

    Is it possible to automate the process and have each bar become a solid color dependent on the value, though? I messed with the format dialogue options as you mentioned and haven't discovered a way to do this yet.

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

    Re: Creating full-spectrum color gradients in bar chart based on input value

    You would need to format each data point (bar) based on it's value in relation to a colour spectrum.

    The usual way to have conditional bar colours is to use multiple series with formula to control which series has the data value. Given a full colour spectrum vba would be easier

  5. #5
    Registered User
    Join Date
    12-12-2020
    Location
    Spokane, WA
    MS-Off Ver
    2016
    Posts
    6

    Re: Creating full-spectrum color gradients in bar chart based on input value

    Quote Originally Posted by Andy Pope View Post
    You would need to format each data point (bar) based on it's value in relation to a colour spectrum.

    The usual way to have conditional bar colours is to use multiple series with formula to control which series has the data value. Given a full colour spectrum vba would be easier
    Yeah, I've been messing with the VBA; any advice on where to go from here with questions regarding it?

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

    Re: Creating full-spectrum color gradients in bar chart based on input value

    you can use macro recorder to get setting the colour of a data point.

    The big question is how do you know what colour to use for any given data value?

  7. #7
    Registered User
    Join Date
    12-12-2020
    Location
    Spokane, WA
    MS-Off Ver
    2016
    Posts
    6

    Re: Creating full-spectrum color gradients in bar chart based on input value

    It would scale from dark red at 1 through orange > yellow > green > blue > to bright cyan after 200. I can mess with that in practice once I'm able to use a macro to set a color for each value. Any advice on setting that up? So far I have this VBA module but it seems overly complicated and doesn't seem to work:

    - VBA Sheet:

    Please Login or Register  to view this content.
    - Module:

    Please Login or Register  to view this content.
    Last edited by Kadai; 01-09-2021 at 04:46 PM.

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

    Re: Creating full-spectrum color gradients in bar chart based on input value

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

    Next time don't forget the code tags.


    Try this code
    Please Login or Register  to view this content.
    In the example you will need to update the RGB values to suit your spectrum
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    12-12-2020
    Location
    Spokane, WA
    MS-Off Ver
    2016
    Posts
    6

    Re: Creating full-spectrum color gradients in bar chart based on input value

    Thanks. In the sheet I'm able to adjust the number values, but changing the RBG values doesn't seem to affect the bar colors. Am I missing something? Also not sure what I should be filling the x cells in with. I'm using 2016 if that makes any difference for the VBA. My end goal is to have the bar length and color generated by the value (each stat would have the same spectrum applied); not sure if this would work with this code?

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

    Re: Creating full-spectrum color gradients in bar chart based on input value

    You would need to change the value in B2:B7 in order for the code to reformat the columns.

    If the values are formula then you will need to use the calculate event

  11. #11
    Registered User
    Join Date
    12-12-2020
    Location
    Spokane, WA
    MS-Off Ver
    2016
    Posts
    6

    Re: Creating full-spectrum color gradients in bar chart based on input value

    I'm able to change those values as you said, but it only alters the length of the bar; the colors are still rigid for each cell; is there a way to make each cell map to the same spectrum? I'll be inputting raw values so that aspect shouldn't be an issue.

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

    Re: Creating full-spectrum color gradients in bar chart based on input value

    As you have not posted an example file I can only go on the code.

    The bar length is determined by values in B2:B7, the colour of the bars is determined by content of C2:C7, which you call rank_rng. If the values in C2:C7 are not somehow based on the values of B2:B7 then they will not change, therefore the colour for each point will not change.

+ 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. [SOLVED] Scatter chart: formulaic rather than based on a full table, is it possible?
    By QuieT in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 04-19-2020, 02:36 AM
  2. Spectrum Chart
    By travelkid in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 01-07-2019, 06:18 AM
  3. Apply Color to chart based on Data source text color
    By ajm1991 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-07-2016, 08:41 PM
  4. [SOLVED] Vba - change column graph color gradients with macro
    By xandermacleod in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 12-14-2012, 03:05 AM
  5. Cell values translated to color gradients: macro needed
    By DudeHey in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-08-2011, 11:57 AM
  6. How create full spectrum 9 by 5 and 10 by 5 matrix?
    By flexalong in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-15-2008, 09:07 PM

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