Select Case in Time Format

[AZGJC]

I'm trying to write some code to select a case based on an interval of time. For example, if the value in the cell is between 8:00 and 8:59, I want it to shade a certain color. However VBA does not like time formats in the case statement, at least the way I'm writing it.

Case 8:00:00 To 8:59:00
Selection.Interior.ColorIndex = 32

Does anyone have an idea on how to use time format in a case statement? Last resort I guess I can have it convert to a number format, do the case statements, then convert back to time format. Thanks!

 

[PHV]

Perhaps this ?
Case #8:00:00# To #8:59:59#

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AZGJC]

That does the trick, thanks!

 

[AnotherHiggins]

You can depend on the fact that the first condition met will the be case selected, so you don't need to do ranges every time, just test to see if it is less than 9:00, then the next case is < 10:00.

Like this:

...
Case Is < TimeValue("9:00:00")
    oCell.Interior.ColorIndex = 32
Case Is < TimeValue("10:00:00")
    oCell.Interior.ColorIndex = 6
...

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.