Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Help needed for a Macro

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am creating a Macro to do the following..

    Comapring two cells containing date values.
    If Date1 - Date 2 > 7 days, copy a GREEN coloured circle object from other cell and paste it in another cell.
    If "Date1 - Date2 < 7 days ", copy a RED coloured object from other cell and paste it into another cell.
    I have created a macro using IF statement but it is working only the first time. But If I change the Date values, macro is not recognizing the change, again pasting the previous colour.
    Please help me..

    Thanks in advance


  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi VK,

    I'm sure this is an easy thing to do with an event macro, but can't give specific instructions without knowing what the green and red objects you are referring to really are. They could be Shape objects (pictures, graphics from the Drawing toolbar, etc.) cannot actually reside in cells, but nevertheless can be positioned on top of the cells of interest so that they look as if they are "in" the cells. On the other hand, these "objects" could simply be special font characters such as the characters in the Wingdings font. To move these from one cell to another requires that the macro delete them out of the source cell, and add them to the destination cell while setting the font color--not a copy/paste type operation.
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again VK,

    In re-reading your question, I see that you already know how to do the paste operation, and that your question is how to get the macro to execute when the dates change. You didn't mention how you are doing it now. Are you using the worksheet's Change event? If so, it will only trigger if the date cells (or any other cells) are MANUALLY edited.

    If you want the macro to trigger whenever the worksheet is calculated, whether manually edited or not, use the Calculate event.

    Also be aware that if the graphic is a font character, the macro's act of entering it into the cell will trigger the Change event again, and possibly also the Calculate event if any other cells are dependent on it. This could lead to an infinite event loop that you will have to deal with.

    Happy computing.

    Damon

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the clarification Damon. I am editing the Date values in the cell maunally and running the macro using short cut key.
    The recorded / modfied macro is as follows..

    If ("L3") > ("E3") Then
    Range("I4").Select
    ActiveSheet.Shapes("Oval 3").Select
    Selection.Copy
    Range("C17").Select
    ActiveSheet.Paste
    Range("C17").Select
    Else
    Range("I6").Select
    ActiveSheet.Shapes("Oval 5").Select
    Selection.Copy
    Range("D17").Select
    ActiveSheet.Paste
    Range("D17").Select
    End If
    End Sub

    Its working fine first time. But when changed the data in "L3" and "E3"cells such a way that "L3" < "E3" then the "Else" part is not executed. Please take a look at the above Macro.
    Thanks a lot.

  5. #5
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again VK,

    Okay, that helps--in fact it is now clear what the problem is. In you line of code

    If ("L3") > ("E3") Then

    ("L3") will ALWAYS be greater than ("E3") because these two values are not cells, they are simply strings, and the string "L3" is always later in the ASCII sorting sequence than "E3". The correct form would be

    If [L3] > [E3] Then

    I was also surprised to see that you used Copy rather than Cut, since each time you run the macro you will get an extra copy of the graphic on your worksheet. If you really intended to do a Cut, just change the Copy method to a Cut method.

    You should also be able to shorten up the code a bit to:

    If [L3] > [E3] Then
    ActiveSheet.Shapes("Oval 3").Copy
    [C17].Select
    ActiveSheet.Paste
    Else
    ActiveSheet.Shapes("Oval 5").Copy
    [D17].Select
    ActiveSheet.Paste
    End If
    Application.CutCopyMode = False

    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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