I have a spreadsheet in which a value is automatically imported into B2
each day.

I would like B3 to automatically show the maximum value ever reached in
B2 and, in B4, the minimum ever reached by B2. Could someone advise me
on how I might do this please.

TIA.

--
F
(Beware of spam trap - remove the negative)

Put the following code in the Worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 Then
If Target = Cells(2, 2) Then
If Cells(2, 2) Cells(3, 2) Then
Cells(3, 2) = Cells(2, 2)
End If
If Cells(2, 2) < Cells(4, 2) Then
Cells(4, 2) = Cells(2, 2)
End If
End If
End If
End Sub

RBS

"F" wrote in message
...
I have a spreadsheet in which a value is automatically imported into B2
each day.

I would like B3 to automatically show the maximum value ever reached in B2
and, in B4, the minimum ever reached by B2. Could someone advise me on how
I might do this please.

TIA.

--
F
(Beware of spam trap - remove the negative)

On 01/09/2006 20:18 RB Smissaert wrote:

Put the following code in the Worksheet module:

Thanks for the help: appreciated. However, I'm not sure what you mean by
the above.

--
Frank
(Beware of spam trap - remove the negative)

Copy the posted code
Have your workbook open
Press Alt + F11
Press Ctrl + R
In the left pane right-click the sheet module that belongs
to the sheet on which you want to run the code.
Do View code.
Paste the code in the right hand pane
Back to the sheet with Alt + F11
Try it out

RBS


"F" wrote in message
...
On 01/09/2006 20:18 RB Smissaert wrote:

Put the following code in the Worksheet module:

Thanks for the help: appreciated. However, I'm not sure what you mean by
the above.

--
Frank
(Beware of spam trap - remove the negative)

On 02/09/2006 12:53 RB Smissaert wrote:

Copy the posted code

[snip]

Try it out

Many thanks - it does exactly what I wanted!

It's led me to adapt it and use it in another sheet. This has a value in
B2 updated each day and I've been able to store the maximum ever reached
in J2 and the minimum ever reached in K2.

However, I can't see how to persuade it to work on different values
contained in a range of cells within a column, ie look at B2 and update
J2 or K2, then look at B3 and update J3 or K3 and so on down to, say,
B27 and update J27 or K27.

Further help would be extremely welcome.

--
Frank
(Beware of spam trap - remove the negative)

Just repeat this bit, but with different values to account
for the different cells:

If Target = Cells(2, 2) Then
If Cells(2, 2) Cells(3, 2) Then
Cells(3, 2) = Cells(2, 2)
End If
If Cells(2, 2) < Cells(4, 2) Then
Cells(4, 2) = Cells(2, 2)
End If
End If

So, you will get 20 or so of these chunks of code.
You could make it a bit better looking and faster to code by running a loop,
something like:

Dim i as long

For i = 2 to 27
If Target = Cells(i, 2) Then
If Cells(i, 2) Cells(i + 1, 2) Then
Cells(i + 1, 2) = Cells(i, 2)
End If
If Cells(i, 2) < Cells(i + 2, 2) Then
Cells(i + 2, 2) = Cells(i, 2)
End If
End If
Next i


RBS


"F" wrote in message
...
On 02/09/2006 12:53 RB Smissaert wrote:

Copy the posted code

[snip]

Try it out

Many thanks - it does exactly what I wanted!

It's led me to adapt it and use it in another sheet. This has a value in
B2 updated each day and I've been able to store the maximum ever reached
in J2 and the minimum ever reached in K2.

However, I can't see how to persuade it to work on different values
contained in a range of cells within a column, ie look at B2 and update J2
or K2, then look at B3 and update J3 or K3 and so on down to, say, B27 and
update J27 or K27.

Further help would be extremely welcome.

--
Frank
(Beware of spam trap - remove the negative)