+ Reply to Thread
Results 1 to 5 of 5

Traffic Lights - Conditional Formatting

  1. #1
    Registered User
    Join Date
    04-24-2012
    Location
    Vancouver
    MS-Off Ver
    Excel 2007
    Posts
    6

    Traffic Lights - Conditional Formatting

    Greetings,

    I'm currently trying to use traffic lights to do the following for me in a particular cell:

    1. Show a yellow light if the value of the cell is <100%
    2. Show a green light if the value of the cell is = 100%
    3. Show a red light if the value of the cell is >100%

    Is this possible? I'm having a hard time getting around this one since the red light option is always less than your previous option (yellow).

    Thanks in advance!

  2. #2
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: Traffic Lights - Conditional Formatting

    Quote Originally Posted by Moe2 View Post
    I'm having a hard time getting around this one since the red light option is always less than your previous option (yellow).
    Actually based on your example, the yellow is less than the red

    You can use conditional formatting.

    Select the data to format
    Select menu data/conditional formatting
    Select the last option "use a formula to determine format"
    Write a formula that evaluates to true. Eg if your range is A1:A20' then type =A1=1
    Then select format (green)
    Repeat with formulas =A1>1 format red & =A1<1 format yellow

  3. #3
    Registered User
    Join Date
    04-24-2012
    Location
    Vancouver
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Traffic Lights - Conditional Formatting

    Thanks for the quick reply. I've attached a screenshot of what I mean - the last option in the icon set defaults to being less than the prior option. I don't have an option to make my red option >1.

    Capture.PNG

  4. #4
    Registered User
    Join Date
    04-03-2012
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Traffic Lights - Conditional Formatting

    Are you totally set on having traffic lights for your formatting? The traffic lights style is designed to be in a specific order, either Red > Yellow > Green or Green > Yellow > Red. You can't set it to Red > Green > Yellow.

    There are a lot of other options though, like what Mallycat suggested.

  5. #5
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: Traffic Lights - Conditional Formatting

    In your image, you will see the icons in the bottom right hand corner from Top to bottom are Green, Yellow, Red, Change the Red one to Yellow, and the Yellow one to red

+ 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