Conditional formatting of an autoshape

[jeff g]

Does anyone know how to change the color of an autoshape
based on an answer given in another cell of the same
workbook?

I need to be able to turn an autoshape red, yellow and
green based on criteria established by my client.

Thanks,

Jeff

 

[Dave Peterson]

If that cell changes value based on typing, then you could use a worksheet
event:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myColor As Long
Dim myShape As Shape

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub

Set myShape = Worksheets("sheet1").Shapes("autoshape 1")

Select Case LCase(Target.Value)
Case Is = "a": myColor = 53
Case Is = "b": myColor = 33
Case Else
myColor = 0
End Select

If myColor = 0 Then
myShape.Fill.Visible = False

Else
With myShape.Fill
.Visible = True
.ForeColor.SchemeColor = myColor
End With
End If

End Sub

Right click on the worksheet tab that contains that cell. Select view code and
paste this in.

Change the address and the shape's name (and rules for colorizing--I recorded a
macro to get the colors I wanted).