VBA Sleep code time interval question

Hello, I found some code on a site that can function similar to the codeline: Application.Wait (Now + TimeValue("0:00:01"))

The code is:

Please Login or Register  to view this content.

Currently, I have an event code that changes a shapes color if data is entered in a particular cell. Unfortunately though, it can only do 1 second intervals with the application.Wait codeline. The code I have doing this is as follows:

Please Login or Register  to view this content.

Is it possible to somehow use the sleep 500 in my event code to do the half second intervals to replace the applicaton.wait aspect? How might this be done if so?

Thank you for your time!

Create a new standard module or use an existing one (like Module1) and add the API code at the top of the module

Please Login or Register  to view this content.

Then in your worksheet module update your existing code:

Please Login or Register  to view this content.

Thanks Jeff, I tested this out but it actually isn't changing the shapes color after I click "OK" on the message box that pops up when typing something into J34. It was changing the color before with the Application.Wait line but for this after clicking OK, it stays the same color and has a bit of a delay before you can click in another cell. That delay in being able to click was expected, but the lack of color change wasn't. Were you getting a different result?

I don't have your file so there was no way for me to test it. Attach your file and I'll be happy to look.

I couldn't get Sleep to work either, even with DoEvents. At a guess I'd say the API overrides the vba.
You can use Wait with < 1 second though.

Please Login or Register  to view this content.

You could add:

Please Login or Register  to view this content.

after the line that makes it red.

By the way, the VBA7 declaration is wrong - dwMilliseconds should always be a Long, not a LongPtr. It's just a number.

Originally Posted by PitchNinja

I tried ByteMarks solution and it works

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking $A addreputationiconsmall.jpg below their profile in any of their posts.

Ah okay yep now it's working. That's really neat! So if I want to change the amount of color flashes, I just change the for i = 1 to 3 to something else. Going to play with this a bit. Thanks for your help again!