Force Sheet to refresh every XX minutes

[Grimace]

Hi all

I am setting up a draw for a competition, and want to make it as easy as possible for the people to read.

To assist in this, i want to highlight any rounds in the draw that have already been started, therefore directing their eyes to the next round to be played. To achieve this, i am just using a conditional format to change the background colour once the Now() time is past the start time of that round.

My issue is that unless i manually change something on the sheet, the formula =now() does not refresh.

It could be an extended period between making changes, so i want to get it to refresh every 5 minutes, that way if the start time for a round has passed it will highlight.

Is there a function to force the sheet to refresh periodically?

Thanks in advance

Darren.

[AlvaroSiza]

How about:

In the ThisWorkbook code section:

Please Login or Register  to view this content.

And in a standard module:

Please Login or Register  to view this content.

The .calculate should envoke a refresh. Please reply with results as this is untested.

[Grimace]

Thanks for that .... sorry to sound vague ... is a standard module just under Sheet 1 etc?

[Grimace]

I just tried it and received a "Compile Error"

Compile Error.jpg

[Grimace]

Any ideas on the compile error please?

[humdingaling]

dont know if you resolved this yourself but see attached sheet with working module

though do not understand how the conditional formatting bit would work
you update now once then what happens?

you would actually need something more on...off ...on..off etc wouldnt you?

this is what i am referring to
http://msdn.microsoft.com/en-us/libr.../ff193220.aspx

alter for your need of course

[Grimace]

Thanks Humdingaling

The formatting piece is done by setting the Conditional formatting against the time.
So as the sheet goes through and autorefreshes, if the time is earlier than now(), it changes the formatting.
The issue i had was keeping it updating the now() function regularly ... will give this a shot thanks

[humdingaling]

reason i asked is because now() will always be now() when you update it

how does it know the different between now() and the now() you did 5 minutes ago...that is why i suggested the "blinking" cell