help with VBA code re comparing cells and generating message with detailed info

[Ross]

Hi there,

I wonder if someone can help me with code to deal with a particular
issue:

I have two columns of cells which capture two different pieces of data
in real time. Call them X and Y, with an X and a Y in every row (or
most rows). One of the pieces of data comes with a time stamp in the
data source. The other piece of data I time stamp through VBA code
(i.e. checking when that part of the worksheet changes and saving that
time). So both X and Y have a column saying what time they arrived.

What I need to do is, checking the entire column of individual X and Y
values on a by-row comparison is "If X > Y and the time X arrived is
after the time Y arrived then pop up a message box saying 'X exceeded
Y at zz:zz time."

Obviously it would be easy to make a cell which could compare X and Y
and the times, but I need to do this through code because I need the
message box to have the detailed info about X and Y and the times at
the moment the message is triggered. And also because X and Y will
change frequently so a cell without a persistent message (until
someone closes it) is likely to be missed.

any help would be greatly appreciated!

if you can, please e-mail me directly in addition to responding to the
group.

thanks in advance,
Ross
(e-mail address removed)

 

[J.E. McGimpsey]

One way:

Assume X is in column A, datestamp for X in column B, Y in column C,
datestamp for Y in column D (adjust as needed):

Public Sub CheckValuesAndDateStamps()
Dim cell As Range
For Each cell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With cell
If .Value > .Offset(0, 2).Value Then
If .Offset(0, 1).Value > .Offset(0, 3).Value Then _
MsgBox "Row " & cell.Row & ":" & vbNewLine _
& .Value & " exceeded " & _
.Offset(0, 2).Value & " at " & _
Format(.Offset(0, 1).Value, "hh:mm") & _
" time."
End If
End With
Next cell
End Sub

 

[Ross]

Is there a way to do this by naming ranges instead of using columns
and offsets (in case I insert or delete columns which then make the
offset numbers wrong)?

For example compare range("xvalues") to range("yvalues") and
range("xtimes") to range("ytimes") one row at a time?

Thanks again
Ross