Hi, I'm quit new to using excel (07) but have so far been very impressed by
both it's features and it's ease of use - However I have stumbled upon a
problem that I cannot get around - PLEASE help me if you can...

I have a formula that returns a numeric value to a specific cell, Say A1,
this number will always be in the range of 0 to 1,000. A number of different
variables influence what the returned value is and this value changes
approximately 5 times per second. I want to be able to show the highest and
lowest value achieved in cells B1 (highest) and B2 (lowest) and have these
two cells both start out at 0 but auto update as a new high or low is
returned in A1

Any ideas on how I can achieve this or where I can go to find an answer
would be great.

Many Thanks,

Mike.

In B1 enter -1
In B2 enter 1001

In the worksheet code area enter:

Private Sub Worksheet_Calculate()
Set a1 = Range("A1")
v = a1.Value
Set b1 = Range("B1")
Set b2 = Range("B2")
Application.EnableEvents = False
If v b1.Value Then
b1.Value = v
End If

If v < b2.Value Then
b2.Value = v
End If
Application.EnableEvents = True
End Sub


--
Gary''s Student - gsnu200802


"Highbury 1" wrote:

Hi, I'm quit new to using excel (07) but have so far been very impressed by
both it's features and it's ease of use - However I have stumbled upon a
problem that I cannot get around - PLEASE help me if you can...

I have a formula that returns a numeric value to a specific cell, Say A1,
this number will always be in the range of 0 to 1,000. A number of different
variables influence what the returned value is and this value changes
approximately 5 times per second. I want to be able to show the highest and
lowest value achieved in cells B1 (highest) and B2 (lowest) and have these
two cells both start out at 0 but auto update as a new high or low is
returned in A1

Any ideas on how I can achieve this or where I can go to find an answer
would be great.

Many Thanks,

Mike.

I couldn't get this to work in 2003 until I amended the Private Sub to

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

everything else the same

very useful Gary's student

edvwvw

Gary''s Student wrote:
In B1 enter -1
In B2 enter 1001

In the worksheet code area enter:

Private Sub Worksheet_Calculate()
Set a1 = Range("A1")
v = a1.Value
Set b1 = Range("B1")
Set b2 = Range("B2")
Application.EnableEvents = False
If v b1.Value Then
b1.Value = v
End If

If v < b2.Value Then
b2.Value = v
End If
Application.EnableEvents = True
End Sub

Hi, I'm quit new to using excel (07) but have so far been very impressed by
both it's features and it's ease of use - However I have stumbled upon a
[quoted text clipped - 14 lines]

Mike.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200809/1

Thanks so much for that, works like a charm....

"Gary''s Student" wrote:

In B1 enter -1
In B2 enter 1001

In the worksheet code area enter:

Private Sub Worksheet_Calculate()
Set a1 = Range("A1")
v = a1.Value
Set b1 = Range("B1")
Set b2 = Range("B2")
Application.EnableEvents = False
If v b1.Value Then
b1.Value = v
End If

If v < b2.Value Then
b2.Value = v
End If
Application.EnableEvents = True
End Sub


--
Gary''s Student - gsnu200802


"Highbury 1" wrote:

Hi, I'm quit new to using excel (07) but have so far been very impressed by
both it's features and it's ease of use - However I have stumbled upon a
problem that I cannot get around - PLEASE help me if you can...

I have a formula that returns a numeric value to a specific cell, Say A1,
this number will always be in the range of 0 to 1,000. A number of different
variables influence what the returned value is and this value changes
approximately 5 times per second. I want to be able to show the highest and
lowest value achieved in cells B1 (highest) and B2 (lowest) and have these
two cells both start out at 0 but auto update as a new high or low is
returned in A1

Any ideas on how I can achieve this or where I can go to find an answer
would be great.

Many Thanks,

Mike.