Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Traffic Lights
Im trying to find a way to, change the colour of a circle from white to
green, yellow or red according to a range of values in a excel cell. Unfortunately, Im not experience in programming and Im not able to build the code that can allow the customization of this circles that will function as €śtraffic lights€ť. Regards, Pedro Serra |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Traffic Lights
Andy Pope has an example on his website at
http://www.andypope.info/charts/trafficlight.htm. A simpler way might be just to use coloured cells. This is extracted from a web page that I am building Another useful technique that is used in management reports is to highlight the data using the 'traffic lights' technique, on target items are in green, items slightly slipping are shown in yellow, and big slippages are shown in red. The will be flagged as follows: * green - estimated cost is less than or equal to budget * yellow - estimated cost.is less than or equal to budget + 25% * red - estimated cost is greater than budget + 25% As well as showing the lights colours, the background is shaded to provide emphasis. The conditional formatting will be formula driven, and to avoid showing a light on a blank line kit tests for no budget, as well as the spend status. It would be very simple to do the highlighting by shading a cell, maybe the status cell or one of the amount cells, such as the budget. Whilst this works, it would be better to show as a traffic light shows it, a coloured circle. This is achieved by using the Winding font. To create this traffic lights, follow these steps: * Step 1. Select the first data row in the 'Status' column Set the font of this cell to 'Winding' Enter a lower-case letter l in the cell Centre the text in this cell Copy this cell down to the appropriate number of rows. * Step 2. Goto Menu FormatConditional Formatting Change Condition 1 to Formula Is Add a formula of =AND(B2<"",D2<=B2) Click the Format button Select the Font Tab Select bright green from the Color dropdown Select the Pattern Tab Select dark green from the palette OK * Step 3. Goto Menu FormatConditional Formatting Change Condition 1 to Formula Is Add a formula of =AND(B2<"",D2<=B2*1.25) Click the Format button Select the Font Tab Select yellow from the Color dropdown Select the Pattern Tab Select dark green from the palette OK * Step 4. Goto Menu FormatConditional Formatting Change Condition 1 to Formula Is Add a formula of =AND(B2<"",D2B2*1.25) Click the Format button Select the Font Tab Select red from the Color dropdown Select the Pattern Tab Select dark green from the palette OK OK the dialog, and it is all done. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Pedro Serra" wrote in message ... I'm trying to find a way to, change the colour of a circle from white to green, yellow or red according to a range of values in a excel cell. Unfortunately, I'm not experience in programming and I'm not able to build the code that can allow the customization of this circles that will function as "traffic lights". Regards, Pedro Serra |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting using Traffic Lights | Excel Discussion (Misc queries) | |||
Conditional format - 3 traffic lights | Excel Worksheet Functions | |||
Conditional Formatting using 3 Traffic Lights Formula | Excel Worksheet Functions | |||
How do I use traffic lights in excel | Excel Discussion (Misc queries) | |||
How do I get traffic lights in excel that change colour | Excel Worksheet Functions |