How to Loop?

[Rob]

Hi,
With reference to the code below. I would like to loop this code at specific
one minute time intervals two cells to the right. The idea is to have the
loop running from 10am to 4pm. Assuming for example that cell i6 is the
active cell at 10am and then at 10:01am k6 becomes the active cell.

Sub CheckVolumeRise2()
If Range("i6") < Range("k6") And Range("k6") < Range("m6") Then
CriteriaReached.Show
End If
End Sub

So at 10:01am the routine would be checking k6 < m6 <o6.

Please will someone show me how to implement this.

Thank you.

 

[Bob Phillips]

Private nTime As Double

Public Sub StartJob()

nTime = TimeValue("10:00:00")
Application.OnTime nTime, "CheckVolumeRise2"
End Sub

Public Sub CheckVolumeRise2()
Static rng As Range

If rng Is Nothing Then Set rng = Range("I6")

If rng.Value < rng.Offset(0, 2).Value And _
rng.Offset(0, 2).Value < rng.Offset(0, 4).Value Then

CriteriaReached.Show
End If

If Now < TimeValue("16:00:00") Then

nTime = nTime + TimeValue("00:01:00")
Application.OnTime nTime, "CheckVolumeRise2"
End If
End Sub