Forum Discussion
Highest Lowest price from running price
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 M10=100
. if L9 updated 110 then M10=110
. if L9 updated to 90 then M10=110
M10 should be update only for highest values not for lowest values.
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.
29 Replies
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.
- sir ,
How it would be incase of range of item .Data is in L9 :L19 instead of L9 and Max in M9:M19 and MIN O9:O19What does the worksheet look like now?
HansVogelaar Very Very thanks sir, You are super, its working,
