Making a cell change colors with every one second increment

[EdwardSnowden]

Hi everyone! I though I was getting pretty clever with the following code but it did not work at all lol. I am trying to get a range to change from one color to another, every second, for a minute. My formula, theoretically, should change from 1 to 0 every second (it is dividing the second by 2 and seeing if there is a decimal place). Thus, I can't get why it isn't working. Any help would be insanely appreciated!!!

Please Login or Register  to view this content.

[Trevor_S]

Some potential problems I can see:

Please Login or Register  to view this content.

This can never be true, as "now" will always be "now"! So the loop should run forever.
Perhaps what you're intending would be more like:

Please Login or Register  to view this content.

However, assuming that your macro isn't changing the colours at all (rather than just not stopping after a minute), there's clearly something else wrong...

Macro code takes time to execute. Not much time, but your method relies on it executing the following line exactly on a round second:

Please Login or Register  to view this content.

If its a fraction of a millisecond either way, the colour won't change!

I think your best option is with nested loops, and checking whether "now" exceeds a calculated time (as in my suggested code above) rather than matching it exactly. For example:

Please Login or Register  to view this content.

There will always be the issue of what happens if the colour change code takes more than a second to execute.
With my code above, the process will still stop after a minute - if the code runs slowly, you won't get the full 60 changes. At best you're only likely to get 59, as the 60th would be fractionally outside the one minute. If a full 60 changes is more important than the total run time, suggest that you change the outer loop to one woth a basic counter so that it just runs 60 times.

[Logit]

.
Here is an answer to your goal :

Please Login or Register  to view this content.

[EdwardSnowden]

Some potential problems I can see:

Please Login or Register  to view this content.

This can never be true, as "now" will always be "now"! So the loop should run forever.
Perhaps what you're intending would be more like:

Please Login or Register  to view this content.

However, assuming that your macro isn't changing the colours at all (rather than just not stopping after a minute), there's clearly something else wrong...

Macro code takes time to execute. Not much time, but your method relies on it executing the following line exactly on a round second:

Please Login or Register  to view this content.

If its a fraction of a millisecond either way, the colour won't change!

I think your best option is with nested loops, and checking whether "now" exceeds a calculated time (as in my suggested code above) rather than matching it exactly. For example:

Please Login or Register  to view this content.

There will always be the issue of what happens if the colour change code takes more than a second to execute.
With my code above, the process will still stop after a minute - if the code runs slowly, you won't get the full 60 changes. At best you're only likely to get 59, as the 60th would be fractionally outside the one minute. If a full 60 changes is more important than the total run time, suggest that you change the outer loop to one woth a basic counter so that it just runs 60 times.

I'm going to test this and get back to you :D thanks so much! I may have a few questions about the explanation as well.

[EdwardSnowden]

.
Here is an answer to your goal :

Please Login or Register  to view this content.

I appreciate it! ) Is there any way you could explain the logic behind the code? I really want to learn VBA and I hate taking someone else's code if I can't explain it because then I feel I learned nothing lol.

[Logit]

.
Here is my understanding of what the code means. If anyone sees an error please correct. Thanks.

It will be easier to read if you copy the code below and paste it into the VBE for reading.

Please Login or Register  to view this content.