Updating maximum and minimum values from a different sheet

[awolfe]

I have three columns (H, M, and O) that record the current value of a stock, the maximum value it reached since bought, and the minimum value. I want column M and O (max and min) to update to reflect changes in column H. If column H ever rises higher than column M, I want column M to update to the higher value, and I want the same to happen for column O if H drops below the value of column O.

Ex:
H2 = $50, M2 = $75, and O2 = $25
If H2 = $100, I want M2 = $100 and O2 = $25
If H2 = $10, I want M2 = $100 and O2 = $10

Now I had asked this question earlier and received a very helpful answer that worked exactly the way I needed it to. I was told to use the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 8 Then
Application.EnableEvents = False
If Target.Value > Target.Offset(0, 5).Value Then
Target.Offset(0, 5).Value = Target.Value
ElseIf Target.Value < Target.Offset(0, 7).Value Then
Target.Offset(0, 7).Value = Target.Value
End If
Application.EnableEvents = True
End If
End Sub

That worked for when I manually updated the values in column H, but I have since tweaked my workbook to update column H automatically using an MSN Money connection in a different sheet, which I named 'Stock Quotes'. Now column H in the 'Portfolio' sheet updates from the 'Stock Quotes' sheet automatically from the internet. Column H in the 'Portfolio' sheet now reads:

H2 = 'Stock Quotes'!D4
H3 = 'Stock Quotes!D5, etc.

Now I've come across the problem of columns M and O not updating to match the maximum and minimum values when column H updates to match the 'Stock Quotes' sheet when it automatically refreshes every minute.

My question now is how can I change the above code to update columns M and O to still show the maximum and minimum values reached by column H, which now uses the 'Stock Quotes' sheet to update, as opposed to me manually typing in the values?

I'm a complete beginner when it comes to VBA, so don't assume anything

I'm also using Excel 2007 if that has an effect on anything.

Thanks a lot!

 

[VoG]

You may be able to use the Calculate event like this:

Private Sub Worksheet_Calculate()
Dim LR As Long, i As Long
LR = Range("H" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    If Range("H" & i).Value > Range("M" & i).Value Then
        Range("M" & i).Value = Range("H" & i).Value
    ElseIf Range("H" & i).Value < Range("O" & i).Value Then
        Range("O" & i).Value = Range("H" & i).Value
    End If
Next i
End Sub

This goes in the same place as your other code.

 

[awolfe]

Based on my quick initial tests of changing some values around by hand, the code looks like its working great already!

I'll let you know how it works later today for the real thing (NYSE opens at 9:30 am est)

Thanks again!

 

[awolfe]

okay, all's well!

Thank you very much!