Hello,

How do I run a simple macro, based on a cell condition.

Ex. If A1 is equal to 1, then run a macro that changes a shape from the
drawing tool box, like the cloud to red. If A1 returns to 0, then run
macro to change the cloud color to blue.

Thanks,
EMoe


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

Hi EMoe,

Try:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Me.Shapes(1).Fill.ForeColor
Select Case Range("A1").Value
Case 1: .SchemeColor = 2
Case 0: .SchemeColor = 12
Case Else: .SchemeColor = 11
End Select
End With
End Sub

Amend the numeric in Shapes(1) to the index number or name of the cloud.

This event code needs to br inserted in the sheet module: right-click the
sheet tab | View Code | paste.


---
Regards,
Norman



"EMoe" wrote in message
...

Hello,

How do I run a simple macro, based on a cell condition.

Ex. If A1 is equal to 1, then run a macro that changes a shape from the
drawing tool box, like the cloud to red. If A1 returns to 0, then run
macro to change the cloud color to blue.

Thanks,
EMoe


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

Hi EMoe,

I intended to use the Change event ( rather than the SelectionChange event):

Private Sub Worksheet_Change(ByVal Target As Range)
With Me.Shapes(1).Fill.ForeColor
Select Case Range("A1").Value
Case 1: .SchemeColor = 2
Case 0: .SchemeColor = 12
Case Else: .SchemeColor = 11
End Select
End With
End Sub

---
Regards,
Norman



"EMoe" wrote in message
...

Hello,

How do I run a simple macro, based on a cell condition.

Ex. If A1 is equal to 1, then run a macro that changes a shape from the
drawing tool box, like the cloud to red. If A1 returns to 0, then run
macro to change the cloud color to blue.

Thanks,
EMoe


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

And, preferable would be:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
With Me.Shapes(1).Fill.ForeColor
Select Case Me.Range("A1").Value
Case 1: .SchemeColor = 2
Case 0: .SchemeColor = 12
Case Else: .SchemeColor = 11
End Select
End With
End If
End Sub

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi EMoe,

I intended to use the Change event ( rather than the SelectionChange
event):

Private Sub Worksheet_Change(ByVal Target As Range)
With Me.Shapes(1).Fill.ForeColor
Select Case Range("A1").Value
Case 1: .SchemeColor = 2
Case 0: .SchemeColor = 12
Case Else: .SchemeColor = 11
End Select
End With
End Sub

---
Regards,
Norman

Sorry, but I tried all 3 codes in ''this workbook", as well as in module
1, but the code didn't change the color of the shape.

The name of the cloud is AutoShape 1.

Here's how the code looked in my window:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
With Me.Shapes(AutoShape1).Fill.ForeColor
Select Case Me.Range("A1").Value
Case 1: .SchemeColor = 2
Case 0: .SchemeColor = 12
Case Else: .SchemeColor = 11
End Select
End With
End If
End Sub

I even tried ("AutoShape 1") in parenthesis, but still no dice.

What did I miss?
EMoe


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

Put it behind the worksheet that contains the shape.

(rightclick on the worksheet tab, select view code and paste it there.)

(Remove it from the ThisWorkbook and the Module1 modules.

EMoe wrote:

Sorry, but I tried all 3 codes in ''this workbook", as well as in module
1, but the code didn't change the color of the shape.

The name of the cloud is AutoShape 1.

Here's how the code looked in my window:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
With Me.Shapes(AutoShape1).Fill.ForeColor
Select Case Me.Range("A1").Value
Case 1: .SchemeColor = 2
Case 0: .SchemeColor = 12
Case Else: .SchemeColor = 11
End Select
End With
End If
End Sub

I even tried ("AutoShape 1") in parenthesis, but still no dice.

What did I miss?
EMoe

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

--

Dave Peterson

Works like a charm now.

By the way...

Is there a way, by looking at a code, to know where it goes; in
module, in this workbook, or this worksheet?

Thanks a bunch again for your help.

Regards,
EMo

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