Hi All-
Need some help. Have a spreadsheet that I'm trying to create a macro that once a cell that has a time-based formula in it... displaying "CLOSE"/"OPEN" based on the current time {=NOW()-TODAY()} reaches a certain criteria {based on two static times referenced 2 other cells} it will copy and paste-special one of two ranges into a thrid range.


Spreadsheet Data:
C89="8:00 AM"... OPEN TIME
C90="3:00 PM"... CLOSE TIME
C91=NOW()-TODAY()
B91=IF(AND(C91>=C89,C91<=C90,"OPEN","CLOSE")
Range("I89:J89")=CLOSERANGE
Range("I90:J90")=OPENRANGE
Range("I91:J91")=PASTERANGE


My ultimate goal is to have a macro(s) copy range 'OPENRANGE' & paste special it to range 'PASTERANGE' as soon as the current time passes the OPEN time ( so that OPENRANGE is displayed in the PASTERANGE while the time is between OPEN and CLOSE times)... and for it copy range 'CLOSERANGE' and paste special it to range 'PASTERANGE' once the current time passes the CLOSE time (so that CLOSERANGE is populated in PASTERANGE while the current time is outside the OPEN and CLOSE times).

The MACROs that I wrote to accomplish this are the below, however I cannot figure out why it doesn't work (it does/performs nothing for some reason):

Sub OPENPASTE()
'
' OPENPASTE Macro
' Macro recorded 10/17/2012 by me
'
If B91 = "OPEN" Then
Range("I90:J90").Select
Selection.Copy
Range("I91:J91").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("K96").Select
End If
End Sub


Sub CLOSEPASTE()
'
' CLOSEPASTE Macro
' Macro recorded 10/17/2012 by me
'
If B91 = "CLOSE" Then
Range("I89:J89").Select
Selection.Copy
Range("I91:J91").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("K96").Select
End If
End Sub


PLEASE HELP... thank you in advance!