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

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

Traffic Lights
 

Thanks Bob,

This is very near from what I was looking for but as I said I'm not very
experience in programming and what I need is to have only one circle that can
have 4 colours:

White - value in a cell is null
Green - value in a cell between 1 and 2
Yellow - value in a cell between 2 and 3
Red - value in a cell between 3 and 4

The colour of the circle should change according to the range defined. Do u
think u can send to my e-mail , an example with this,
because I cant discover the way to adapt your worksheet trafficlight to what
I was trying to get.

Thanks in advanced!

Pedro

"Bob Phillips" wrote:

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