Changing Shape colour by a click?

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

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

[email protected] 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

Hi Andy,

I dumped the macro into excel, and assigned it to the autoshape. When
clicked it changes color once, then reverts back to the original color
of the shape, eg, a yellow circle when clicked flashes blue, then
reverts to yellow, a green circle when clicked flashes pink, then
reverts back to green.

Any other suggestions?

Cheers, Tania

Ack! Andy, I am being too blonde for words!

I played with the colors as per your instructions, and it WORKS!
Thank you so much for your time

Next time I will test out properly before coming back with a silly
response!

Tania

No problem glad it worked for you.

[email protected] wrote:
> Ack! Andy, I am being too blonde for words!
>
> I played with the colors as per your instructions, and it WORKS!
> Thank you so much for your time
>
> Next time I will test out properly before coming back with a silly
> response!
>
> Tania
>

--

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