Help needed for a Macro

VK

New Member
Joined
Apr 22, 2002
Messages
2
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top