• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Traffic lights

Chubbyp

New Member
I am creating a project status report using excel. The status of each milestones needs to traffic light Green, Amber, Red. Is there any way that I can click on the traffic light icon and it changes colour? At the moment I am using conditional formatting I need to type in a value to get a colour. The value doesn't mean anything I just need the symbol with the desired colour.... Ideally to just click for the desired colour.. Green, Amber, or Red.
 
One way you can achieve this is by typing in the character small l i.e. the alphabet L in lower case and change the font name to Wingdings

You can change the font color and create a hyperlink etc on this text..

Hope that helps!!
 
simply double click on the icon to scroll through the 3 options

2018-01-03_11-05-12.gif

see attached file

There is extensive use of Conditional Formatting, Named Formula and VBA to achieve this, but the effort is worth it
 

Attachments

  • upload_2018-1-3_10-59-26.png
    upload_2018-1-3_10-59-26.png
    4.8 KB · Views: 36
  • Checklist.xls
    40.5 KB · Views: 44
Last edited:
I want to be able to click the button and it turn the colour I need Red, Amber or Orange. Instead of having to put a number in the cell to get the colour... Or alternatively I will settle for having to type the letters R = Red or A=Amber and G=Green.

upload_2018-1-3_13-59-46.png
 
Traffic lights (non VBA solution)

1] Click cell B2 dropdown list to select "R","A" or "G".
upload_2018-1-3_13-58-37.png

2] The Traffic Light using "Custom Cells Format" +"Data Validation" and "Conditional Formatting" as in :
upload_2018-1-3_14-0-1.png

Regards
Bosco
 

Attachments

  • TrafficLights.xlsx
    182.4 KB · Views: 83
Last edited:
Here is the same file setup for Traffic Lights

View attachment 48527

see attached file:

Thank you a lot for your post. This is exactly what i am looking for. However, when i try to copy the traffic light into my file it does not work anymore. The double click results in a wingdings sign. I know that i miss configurations however i do not know which ones... Could you help me?

Can I just copy paste the content in the checklist?
 
Did you check all the Named Formula are setup correctly?
Hi, Found this great solution for my project status. Unfortunately, I copied and just like Yves Dietiker, I cannot get it to work. It stays red all the time. I cannot figure out what you mean by "Named Formula are setup correctly". I cannot seem to find the right configuration change to make. Can you help?
 
Back
Top