Forum Discussion
Solved
Highest Lowest price from running price
Hi friends this is stock market related issue. L9 cell has a price it shows profit and loss,it changes every second. L9 Profit M10 max profit M12 max loss example 1. if L9 has 100 profit then M...
Start by entering -1000000 in M10 and 1000000 in M12. These are initial values that will be overwritten.
Enter the formula =NOW() in another cell (it doesn't matter where).
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Calculate() Application.EnableEvents = False Range("M10").Value = Application.Max(Range("L9,M10")) Range("M12").Value = Application.Min(Range("L9,M12")) Application.EnableEvents = True End SubSwitch back to Excel.
Save the workbook as a macro-enabled workbook.
Make sure that you allow macros when you open it.
Try this version:
Private Sub Worksheet_Calculate()
Dim r As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
For r = 9 To 19
If Range("L" & r).Value <> "" Then
If Range("M" & r).Value = "" Then
Range("M" & r).Value = Range("L" & r).Value
Range("O" & r).Value = Now
ElseIf Range("L" & r).Value > Range("M" & r).Value Then
Range("M" & r).Value = Range("L" & r).Value
Range("O" & r).Value = Now
End If
End If
If Range("K" & r).Value <> "" Then
If Range("N" & r).Value = "" Then
Range("N" & r).Value = Range("K" & r).Value
Range("P" & r).Value = Now
ElseIf Range("K" & r).Value < Range("N" & r).Value Then
Range("N" & r).Value = Range("K" & r).Value
Range("P" & r).Value = Now
End If
End If
Next r
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Sir,
Perfectly fine. Thank you very much. You are a genius.
Perfectly fine. Thank you very much. You are a genius.