Hello,
I have this code that changes the color of AutoShape 517, depending o
the condition of cell E3. How do I get 2 auto shapes to work on th
same sheet?

I tried to duplicate this code and changed the autoshape # and range t
E4: Got a compile error. I guess a conflict with Worksheet_Calculate()
I also tried to insert With Me.Shapes("AutoShape 626").Fill.ForeColor
Select Case Me.Range("E4").Value


Working Code for one shape:

Private Sub Worksheet_Calculate()
With Me.Shapes("AutoShape 517").Fill.ForeColor
Select Case Me.Range("E3").Value
Case 1: .SchemeColor = 14 ' pink
Case 0: .SchemeColor = 15 ' Light Blue
Case Else: .SchemeColor = 11 ' green
End Select
End With
End Sub

I'm stuck,
EMo

--
EMo
-----------------------------------------------------------------------
EMoe's Profile: http://www.excelforum.com/member.php...fo&userid=2318
View this thread: http://www.excelforum.com/showthread.php?threadid=38672

The following worked very well with me. Note that cell E3 should have
formula. If you are trying to manually edit the value in E3, then us
Worksheet_Change event instead of the calculate.

Private Sub Worksheet_Calculate()

With Me.Shapes("AutoShape 1").Fill.ForeColor
Select Case Me.Range("E3").Value
Case 1: .SchemeColor = 14 ' pink
Case 0: .SchemeColor = 15 ' Light Blue
Case Else: .SchemeColor = 11 ' green
End Select
End With

With Me.Shapes("AutoShape 2").Fill.ForeColor
Select Case Me.Range("E3").Value
Case 1: .SchemeColor = 14 ' pink
Case 0: .SchemeColor = 15 ' Light Blue
Case Else: .SchemeColor = 11 ' green
End Select
End With

End Sub


Manges

--
mangesh_yada
-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=38672

Hi E,

Try the following, which can readily be expanded to deal with additional
shapes:

'===============================
Private Sub Worksheet_Calculate()
Dim ArrShapes As Variant, ArrCells As Variant
Dim i As Long, j As Long

ArrShapes = Array("AutoShape 517", "AutoShape 626")
ArrCells = Array("E3", "E4")

For i = LBound(ArrShapes) To UBound(ArrShapes)

With Me.Shapes(ArrShapes(i)).Fill.ForeColor
Select Case Me.Range(ArrCells(i)).Value
Case 1:
.SchemeColor = 14 ' pink
Case 0:
.SchemeColor = 15 ' Light Blue
Case Else:
.SchemeColor = 3
End Select
End With
Next i
End Sub
'<<===============================

To process additional shapes, simply add the shape's name to the ArrShapes
list and add the address of the controlling cell in ArrCells. For example,
to color "Autoshape 5" dependent on the value of cell A1, amend the
ArrShapes and ArrCells assignment lines to read:

ArrShapes = Array("AutoShape 517", "AutoShape 626", "Autoshape 5")
ArrCells = Array("E3", "E4", "A1")

---
Regards,
Norman




"EMoe" wrote in message
...

Hello,
I have this code that changes the color of AutoShape 517, depending on
the condition of cell E3. How do I get 2 auto shapes to work on the
same sheet?

I tried to duplicate this code and changed the autoshape # and range to
E4: Got a compile error. I guess a conflict with Worksheet_Calculate()
I also tried to insert With Me.Shapes("AutoShape 626").Fill.ForeColor
Select Case Me.Range("E4").Value


Working Code for one shape:

Private Sub Worksheet_Calculate()
With Me.Shapes("AutoShape 517").Fill.ForeColor
Select Case Me.Range("E3").Value
Case 1: .SchemeColor = 14 ' pink
Case 0: .SchemeColor = 15 ' Light Blue
Case Else: .SchemeColor = 11 ' green
End Select
End With
End Sub

I'm stuck,
EMoe


--
EMoe
------------------------------------------------------------------------
EMoe's Profile:
http://www.excelforum.com/member.php...o&userid=23183
View this thread: http://www.excelforum.com/showthread...hreadid=386723