Autoshape color

[brian5857]

I have an autoshape that is an oval (Oval 10). Cell A1 will either have a value of 1 or zero.

Does anyone know VB code that will change the fill color of the oval depending on the value of cell A1?

0=red

1=green

 

[VoG]

If A1 is being changed manually try this. Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then
    Select Case Target.Value
        Case 0: Me.DrawingObjects("Oval 10").Interior.ColorIndex = 3
        Case 1: Me.DrawingObjects("Oval 10").Interior.ColorIndex = 10
    End Select
End If
End Sub

 

[brian5857]

Thanks for the help! That worked good if I manually type in 0 or 1 and then press enter. But it doesnt work in my sheet because the value in cell A1 is changing on its own. The cell has a formula in it that looks at a bit in a PLC and shows if the bit is a 1 or a 0.

 

[VoG]

Try this instead

Private Sub Worksheet_Calculate()
Select Case Range("A1").Value
    Case 0: Me.DrawingObjects("Oval 10").Interior.ColorIndex = 3
    Case 1: Me.DrawingObjects("Oval 10").Interior.ColorIndex = 10
End Select
End Sub

 

[brian5857]

That did it. Thank You. This is a great forum!