Many traffic-light solutions for Excel exist but all the ones I tried only work for a single light, and I needed an array like this:
I wanted to create a light by simply typing a formula in the light’s cell, with the colour of the light determined by a value in cell elsewhere. The light must change colour as soon as the underlying value changes. This wasn’t as simple to implement as I thought, but in the end a bit of VBA led to this formula :
=trafficlight(Sheet2!B4,Sheet2!B$3,Sheet2!C$3)
and the rest of the lights are created by dragging this formula right and down.
The parameters to the TrafficLight function are:
- The cell containing the value which determines the colour of the light.
- The threshold to change from red to amber.
- The threshold to change from amber to green.
in this example, the Sheet2 looks like this:
Project 1 has a value of 64 and thus is amber.
Here’s a ZIP file with the .XLS and the images project-progress-dashboard, feel free to use it as you see fit.
Notes:
- The red and green images have an exclamation mark and a tick superimposed so that they are recognisable on a black-and-white printout.
- You must keep the 3 GIF files in the same directory as the spreadsheet.
- There is one bug. If you resize a cell containing a light such that the light is no longer contained within the cell’s boundaries, the light will not be deleted when its underlying value changes (you end up with an orphaned light). To fix this, there is a “Remove lights” button; clicking it will delete all images and pressing F9 will re-generate correctly.
To demonstrate that the lights change according to the values you supply. As I couldn’t know what process you are trying to document, a random value was my only option.
That said, asking such a question indicates that you probably shouldn’t be using a scheme like this in the first place.
i don’t understand why you are using RAND on the consolidated sheet
Seems to be a problem with Application.Volatile not being recognised. If you press F9 to re-calculate, it displays correctly.
Love the idea, but the calculations do not function. Changing values in the consolidated worksheet has no impact on the red, yellow, green indicators in the GlobalStatus worksheet.
Hi,
just came a across this article by chance.
Great piece of work.
Directly learned a few things.
Thanks!
No problem. Here is a new version, where missing data shows a grey traffic light.
new version
You’ll see the change in the TrafficLight VBA, it’s highlighted with !!!!! marks.
Hi there,
What a fantastic project & article. I’m relativly new to VBA and I’m having little trouble editing your project to include a futher .gif for a Grey status for a ‘no data’/’no plan’ return.
so that when there is no data for a particlar RAG status it would display a another .gif that I’d create called ‘TrafficLightGrey.gif’
Thanks for any help you could give me – but no worries if not!