+ Reply to Thread
Results 1 to 9 of 9

Run macro based on cell condition

  1. #1
    Forum Contributor
    Join Date
    05-09-2005
    Location
    SC
    Posts
    196

    Run macro based on cell condition

    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

  2. #2
    Norman Jones
    Guest

    Re: Run macro based on cell condition

    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" <EMoe.1rwtqh_1120925107.423@excelforum-nospam.com> wrote in message
    news:EMoe.1rwtqh_1120925107.423@excelforum-nospam.com...
    >
    > 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
    >




  3. #3
    Norman Jones
    Guest

    Re: Run macro based on cell condition

    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" <EMoe.1rwtqh_1120925107.423@excelforum-nospam.com> wrote in message
    news:EMoe.1rwtqh_1120925107.423@excelforum-nospam.com...
    >
    > 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
    >




  4. #4
    Norman Jones
    Guest

    Re: Run macro based on cell condition

    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" <normanjones@whereforartthou.com> wrote in message
    news:utWafYKhFHA.1148@TK2MSFTNGP12.phx.gbl...
    > 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
    >




  5. #5
    Forum Contributor
    Join Date
    05-09-2005
    Location
    SC
    Posts
    196

    Change color didn't work

    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

  6. #6
    Dave Peterson
    Guest

    Re: Run macro based on cell condition

    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

  7. #7
    Forum Contributor
    Join Date
    05-09-2005
    Location
    SC
    Posts
    196
    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

  8. #8
    Norman Jones
    Guest

    Re: Run macro based on cell condition

    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" <EMoe.1rxfyb_1120953903.5011@excelforum-nospam.com> wrote in message
    news:EMoe.1rxfyb_1120953903.5011@excelforum-nospam.com...
    >
    > 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
    >




  9. #9
    Forum Contributor
    Join Date
    05-09-2005
    Location
    SC
    Posts
    196
    Thanks,

    EMoe

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1