Traffic Lights

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

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 Format>Conditional 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 Format>Conditional 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 Format>Conditional 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 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" <[email protected]> wrote in message
news:[email protected]...
> 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

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 [email protected], an example with this,
because I can’t 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 Format>Conditional 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 Format>Conditional 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 Format>Conditional 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 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>