Excel Traffic Lights

**Runner77** · 10-20-2009, 08:52 PM

Hi guys,

I am trying to get the brown boxes on the master page to change colour brown for occupied and green for vacant. I would like this to happen when I change the tennancy type in each tab B1,B2,B3 ect.

I was trying to use name ranges to get this to work, But I seem to have stuffed it up.

Thanks in advance for your help.

Runner77

**Chance2** · 10-20-2009, 09:03 PM

I think the issue is in your "Alert" named formula:

You have CHOOSE which is looking for a number, when you you should use IF instead...

Please Login or Register  to view this content.

**teylyn** · 10-20-2009, 09:09 PM

Hi,

maybe you should go through this again step by step

http://exceluser.com/solutions/traffic.htm

**NBVC** · 10-20-2009, 09:28 PM

I think you will need to set up 3 different Alerts...

Alert1 for B1, Alert2 for B2 and Alert3 for B3

In B7:B10 I entered a formula that will give you the position number of each sheets' choice...

.. then the 3 named ranges, Alert1 to Alert3 have individual Choose() formulas...

See attached.