+ Reply to Thread
Results 1 to 11 of 11

Bar changing colour based on cell value

  1. 09-22-2021, 05:29 PM #1
    Kevin Rush
    Kevin Rush is offline
    Forum Contributor
    Join Date
    09-25-2018
    Location
    Grimsby, UK
    MS-Off Ver
    365
    Posts
    148

    Bar changing colour based on cell value

    I have created a very simple chart to show 3 series of data. The data is as follows:
    1. As Built Thickness
    2. Minimum Measured Thickness
    3. Minimum Allowable Thickness


    What I am looking to do is to change the colour of a bar (not the whole series) based on the Current value against the minimum allowable value. If the current value is more that the minimum value I would like the bar to be green, if the current value is less than allowable then I would like it to be red.

    There are 14 set of data, each, with 3 the 3 values listed above.

    For some context, this is for an oil storage tank. I have attached an example spreadsheet. You will see in course 3, that the current value is less than the allowable value.

    Thanks for your time.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 09-22-2021, 05:48 PM #2
    WideBoyDixon
    WideBoyDixon is offline
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Bar changing colour based on cell value

    I don't think you can do this without using VBA. The following macro might work for you but it's clunky:

    Please Login or Register  to view this content.
    WBD
    Office 365 on Windows 11, looking for ✶ rep!
    Reply With QuoteRegister To Reply
  3. 09-22-2021, 05:54 PM #3
    Kevin Rush
    Kevin Rush is offline
    Forum Contributor
    Join Date
    09-25-2018
    Location
    Grimsby, UK
    MS-Off Ver
    365
    Posts
    148

    Re: Bar changing colour based on cell value

    Hi WBD,

    I not opposed to using VBA (The main workbook is VBA dependant) However, this didn't work unfortunately
    Reply With QuoteRegister To Reply
  4. 09-22-2021, 05:57 PM #4
    Kevin Rush
    Kevin Rush is offline
    Forum Contributor
    Join Date
    09-25-2018
    Location
    Grimsby, UK
    MS-Off Ver
    365
    Posts
    148

    Re: Bar changing colour based on cell value

    Apologies, it has worked. I just need to make a button as it doesn't automatically run the macro. Thanks!

    *Edit* It does change back to green if once it has gone red.
    Last edited by Kevin Rush; 09-22-2021 at 06:01 PM.
    Reply With QuoteRegister To Reply
  5. 09-22-2021, 06:35 PM #5
    WideBoyDixon
    WideBoyDixon is offline
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Bar changing colour based on cell value

    Yeah. It's tricky. The series is formatted with green fill. The macro changes points to red that are less than the minimum allowable thickness but I can believe they're easily changed back to green. Not sure it's possible to easily do what you're trying to do.
    Reply With QuoteRegister To Reply
  6. 09-22-2021, 06:39 PM #6
    Kevin Rush
    Kevin Rush is offline
    Forum Contributor
    Join Date
    09-25-2018
    Location
    Grimsby, UK
    MS-Off Ver
    365
    Posts
    148
    I’ve made to macros based on yours. Now I just need to make one to run one then the other.
    Last edited by AliGW; 09-23-2021 at 05:13 AM. Reason: PLEASE don't quote unnecessarily!
    Reply With QuoteRegister To Reply
  7. 09-22-2021, 08:10 PM #7
    PietBom
    PietBom is offline
    Valued Forum Contributor
    Join Date
    10-14-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2019
    Posts
    353

    Re: Bar changing colour based on cell value

    Maybe this is a way to work with Red an Green..........
    I used 2 helper columns were i calculated the data: Green for values Measured Minimum Thickness being less then the Allowed Minimum Thickness
    The second column calculated the values just the opposite condition for Red
    Then i added 1 serie to the chart, for the extra colour Red
    Hope this helps
    Attached Files Attached Files
    Last edited by PietBom; 09-23-2021 at 09:58 AM.
    Kind regards,
    Piet Bom
    Reply With QuoteRegister To Reply
  8. 09-22-2021, 09:23 PM #8
    bsalv
    bsalv is offline
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: Bar changing colour based on cell value

    with events
    change the value of E18
    Attached Files Attached Files
    Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
    Reply With QuoteRegister To Reply
  9. 09-23-2021, 05:02 AM #9
    Pichai_Tee
    Pichai_Tee is offline
    Registered User
    Join Date
    06-17-2020
    Location
    Bangkok, Thailand
    MS-Off Ver
    365
    Posts
    17

    Re: Bar changing colour based on cell value

    No need VBA
    the same concept as PietBom gave the sample file.
    You might search from internet with keyword "Excel Conditional Formatting Bar Chart"
    Reply With QuoteRegister To Reply
  10. 09-23-2021, 05:14 AM #10
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,774

    Re: Bar changing colour based on cell value

    Quote Originally Posted by PietBom View Post
    Maybe this is a way to work with Red an Green..........
    Sorry for off-topic interjection:

    Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks for your help.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  11. 09-23-2021, 10:00 AM #11
    PietBom
    PietBom is offline
    Valued Forum Contributor
    Join Date
    10-14-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2019
    Posts
    353

    Re: Bar changing colour based on cell value

    Extended my reply at #7
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Changing text colour in a cell based on value or colour of another cell.
    By Jeankartel in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-15-2019, 07:16 PM
  2. Changing a cell colour based on value in cells
    By keane2015 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-14-2017, 11:19 AM
  3. Changing a cell colour based on score
    By mikeuk1954 in forum Excel General
    Replies: 2
    Last Post: 07-10-2015, 05:05 AM
  4. Changing the colour of a tab based on a cell value
    By lauz125 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-30-2013, 04:15 AM
  5. Changing the colour of a cell based on the colour of another cell
    By Pete2931 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-11-2012, 03:35 AM
  6. Changing colour of a cell based on several conditions
    By jennapayne in forum Excel General
    Replies: 2
    Last Post: 03-31-2011, 09:03 AM
  7. changing cell colour based on true/false
    By morbo in forum Excel General
    Replies: 4
    Last Post: 08-13-2008, 04:39 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1