Hi,

Try this routine, assign it to the traffic light shapes.
You may need to adjust the values of the colour scheme.

Sub TrafficLight()

With ActiveSheet.Shapes(Application.Caller)
If .Fill.ForeColor.SchemeColor= 2 Then
' red to yellow
.Fill.ForeColor.SchemeColor = 5
ElseIf .Fill.ForeColor.SchemeColor = 5 Then
' yellow to green
.Fill.ForeColor.SchemeColor = 3
ElseIf .Fill.ForeColor.SchemeColor = 3 Then
' green to red
.Fill.ForeColor.SchemeColor = 2
End If
End With

End Sub

Cheers
Andy

ttwilliams@xtra.co.nz wrote:
> I have a spreadsheet, that we want to use for tracking - and use a
> "traffic light" type system. By using a circle (autoshape) and it
> being red "no go", green "OK" and amber "issues". There
> will be an autoshape for each row, indicating the status of the record.
>
> What I am trying to achieve is by clicking on the autoshape it cycles
> through the colors. I don't want to have to have three separate
> circles for each record - with three circles I know how to program by
> a click to change an individual circle to one color. Can someone help
> me ? Appreciate your time/assistance with this.
> Thanks Tania
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info