I am trying to do the following with 4 if statements to reflect the status of individual orders:

Column H = Quantity Start (manually entered)
Column P = Quantity Filled (manually entered)
Column S = Quantity Remaining (formula driven ie. Column H - P)
Column C = Status (formula driven by the 4 IF statements shown below)
Column O = Expiry Date (manually entered)

Column C is the dependent variable showing the status as to whether an order is (1)open; (2) filled; (3) partially
filled; or (4) expired/historical depending on what is entered in Column's H,O,P,S

Eg. Assume today is Jan. 25

(Status) (Quantity Start) (Expiry Date) (Quantity Filled) (Quantity Remaining)
Column C Column H Column O Column P Column S

Open 2000 Jan. 26 2000
Historical 2000 Jan.24 2000
Filled 5000 Jan. 25 5000 0
Partial 5000 Jan. 25 2000 3000
" " " " "
" " " " "
" " " " "
" " " "
" "

Note: mytime = now

I made it so orders expire at noon on the expiry date, i.e., change from open to historical if orders are not filled or
partially filled.

If Range("H" & Target.Row).Value = Range("P" & Target.Row).Value And _
Range("S" & Target.Row).Value = 0 Then
Range("C" & Target.Row).Value = "Filled"
ElseIf Range("H" & Target.Row).Value Range("P" & Target.Row).Value And _
Range("P" & Target.Row).Value < 0 Then
Range("C" & Target.Row).Value = "Partial"
ElseIf mytime Range("O" & Target.Row).Value + 0.5 And Range("P" & Target.Row).Value = 0 And _
Range("H" & Target.Row).Value = Range("S" & Target.Row).Value Then
Range("C" & Target.Row).Value = "Historical"
ElseIf Range("H" & Target.Row).Value = Range("S" & Target.Row).Value And _
Range("P" & Target.Row).Value = 0 Then
Range("C" & Target.Row).Value = "Open"
End If

The difficulty I'm having however is in getting the Status (Column C) for open orders to change to historical through
out the entire (Column C range) when the expiration date/time is elapsed.

Eg.1 Assume today is Jan. 25 at 11:30 am

(Status) (Quantity Start) (Expiry Date) (Quantity Filled) (Quantity Remaining)
Column C Column H Column O Column P Column S

Open 200 Jan. 25 200
Open 500 Jan. 25 500
Open 2000 Jan. 26 2000
Open 800 Jan. 25 800

Eg.2 Assume today is Jan. 25 at 12:01 pm (31 minutes later)

(Status) (Quantity Start) (Expiry Date) (Quantity Filled) (Quantity Remaining)
Column C Column H Column O Column P Column S

Historical 200 Jan. 25 200
Historical 500 Jan. 25 500
Open 2000 Jan. 26 2000
Historical 800 Jan. 25 800

Assume that one end user entered 4 orders (11:30 am) as in Eg. 1. Assume another end user is about to enter an
additional order (12:01 pm), how can I make the status change as in Eg. 2 when they click on any cell on the speadsheet.
In other words what I'm missing is what stimulates the entire C Column to accurately update all the status', just by
clicking on any cell.


Thank you in advance.

Susan