without using a loop...
 

is it possible to write a script that when a user enters a value, that checks
to see if the value is greater than the maximum value of a row?

without using a loop...
 

Where Row 4 contains your data that you want to check against.
in A6 put this formula =IF(MAX(4:4)<B6,"Too big", "OK")
in B6 enter a new value.

Take a look at Data|Validation; that might work for you also.

--
Regards,
John


"Damon "Dutch" Kash" wrote:

is it possible to write a script that when a user enters a value, that checks
to see if the value is greater than the maximum value of a row?

without using a loop...
 

John - this would be great for a formula solution, I was hoping to do this in
VBA script. Thanks, though. Anybody out there have a non-loop VBA answer?

"John Keith" wrote:

Where Row 4 contains your data that you want to check against.
in A6 put this formula =IF(MAX(4:4)<B6,"Too big", "OK")
in B6 enter a new value.

Take a look at Data|Validation; that might work for you also.

--
Regards,
John


"Damon "Dutch" Kash" wrote:

is it possible to write a script that when a user enters a value, that checks
to see if the value is greater than the maximum value of a row?

without using a loop...
 

Damon,

Enter this code into the worksheet module (in XL, right-click the sheet tab
and choose "View Code". This assumes that the user entry is in cell A1 and
that the row to check against is Row 2:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1")) Is Nothing Then
With Rows(2)
If Target.Value WorksheetFunction.Max(.EntireRow) Then
MsgBox "entry is bigger than anything in row 2"
End If
End With
End If

End Sub

hth,

Doug

"Damon "Dutch" Kash" <Damon "Dutch" wrote in
message ...
is it possible to write a script that when a user enters a value, that
checks
to see if the value is greater than the maximum value of a row?

without using a loop...
 

As John said, Data Validation will do it.

If the DV cell and the MAX being checked are in different rows, use DV with
a type Custom and a formula of

=B6<=MAX(4:4)

If in the the same row, use a formula of

=B4<=LARGE(4:4,2)

The different formulae are because in the same row, the cell in question
could be the largest

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Damon Dutch Kash" wrote in
message ...
John - this would be great for a formula solution, I was hoping to do this
in
VBA script. Thanks, though. Anybody out there have a non-loop VBA
answer?

"John Keith" wrote:

Where Row 4 contains your data that you want to check against.
in A6 put this formula =IF(MAX(4:4)<B6,"Too big", "OK")
in B6 enter a new value.

Take a look at Data|Validation; that might work for you also.

--
Regards,
John


"Damon "Dutch" Kash" wrote:

is it possible to write a script that when a user enters a value, that
checks
to see if the value is greater than the maximum value of a row?

without using a loop...
 

Bob,

It took me a bit to realize why the Large worked whether or not the cell in
question is the largest. Very nice.

Doug

"Bob Phillips" wrote in message
...
As John said, Data Validation will do it.

If the DV cell and the MAX being checked are in different rows, use DV
with
a type Custom and a formula of

=B6<=MAX(4:4)

If in the the same row, use a formula of

=B4<=LARGE(4:4,2)

The different formulae are because in the same row, the cell in question
could be the largest

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Damon Dutch Kash" wrote in
message ...
John - this would be great for a formula solution, I was hoping to do
this
in
VBA script. Thanks, though. Anybody out there have a non-loop VBA
answer?

"John Keith" wrote:

Where Row 4 contains your data that you want to check against.
in A6 put this formula =IF(MAX(4:4)<B6,"Too big", "OK")
in B6 enter a new value.

Take a look at Data|Validation; that might work for you also.

--
Regards,
John


"Damon "Dutch" Kash" wrote:

is it possible to write a script that when a user enters a value,
that
checks
to see if the value is greater than the maximum value of a row?

without using a loop...
 

I went through the same thoughts when I came up with it Doug. I managed to
do it in my mind, but when I tried explaining it in words, even to myself, I
didn't convince myself <vbg

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Doug Glancy" wrote in message
...
Bob,

It took me a bit to realize why the Large worked whether or not the cell
in
question is the largest. Very nice.

Doug

"Bob Phillips" wrote in message
...
As John said, Data Validation will do it.

If the DV cell and the MAX being checked are in different rows, use DV
with
a type Custom and a formula of

=B6<=MAX(4:4)

If in the the same row, use a formula of

=B4<=LARGE(4:4,2)

The different formulae are because in the same row, the cell in question
could be the largest

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Damon Dutch Kash" wrote in
message ...
John - this would be great for a formula solution, I was hoping to do
this
in
VBA script. Thanks, though. Anybody out there have a non-loop VBA
answer?

"John Keith" wrote:

Where Row 4 contains your data that you want to check against.
in A6 put this formula =IF(MAX(4:4)<B6,"Too big", "OK")
in B6 enter a new value.

Take a look at Data|Validation; that might work for you also.

--
Regards,
John


"Damon "Dutch" Kash" wrote:

is it possible to write a script that when a user enters a value,
that
checks
to see if the value is greater than the maximum value of a row?