Forum Discussion
Highest Lowest price from running price
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.
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.
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:O19
What does the worksheet look like now?
Thank you. Change the code to
Private Sub Worksheet_Calculate() Dim r As Long Application.ScreenUpdating = False Application.EnableEvents = False For r = 9 To 19 Range("M" & r).Value = Application.Max(Range("L" & r & ",M" & r)) Range("N" & r).Value = Application.Min(Range("L" & r & ",N" & r)) Next r Application.EnableEvents = True Application.ScreenUpdating = True End Sub