Changing Shape colour by a click?

[ttwilliams]

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]

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]

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

 

[ttwilliams]

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]

No problem glad it worked for you.

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