Run macro based on cell condition

[EMoe]

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

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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]

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

[Dave Peterson]

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

[EMoe]

Works like a charm now.

By the way...

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

Thanks a bunch again for your help.

Regards,
EMoe

[Norman Jones]

Hi EMoe,

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

I tried to direct you by including, in my first post, the statement:

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

For clarity, I should have appended this to my follow up posts.

Without such direction you would need to recognise it as an event procedure.
Having done so, all worksheet event procedures are all of the form:

Sub Worksheet_SomeEvent

and all workbook event procedures are of the form

Sub Workbook_SomeEvent

Worksheet event code is placed in the relevant worksheet module; workbook
event code goes in the workbook's ThisWorkbook module.

For a good guide to event procedures, see Chip Pearson at:

http://www.cpearson.com/excel/events.htm

---
Regards,
Norman



"EMoe" <[email protected]> wrote in message
news:[email protected]...
>
> Works like a charm now.
>
> By the way...
>
> Is there a way, by looking at a code, to know where it goes; in a
> module, in this workbook, or this worksheet?
>
> Thanks a bunch again for your help.
>
> Regards,
> EMoe
>
>
> --
> EMoe
> ------------------------------------------------------------------------
> EMoe's Profile:
> http://www.excelforum.com/member.php...o&userid=23183
> View this thread: http://www.excelforum.com/showthread...hreadid=385841
>

[EMoe]

Thanks,

EMoe