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.
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
Sir,
Have tried to modify and want to add timestamp whenever Max and Min will updated. The timestamp will also get updated accordingly.
But manual timestamp is working not the AutoMax and Min.
Please check the attached
Thanks in advance.
Private Sub Worksheet_Calculate() Dim r As Long Application.ScreenUpdating = False Application.EnableEvents = False For r = 9 To 19 If Range("L" & r).Value > Range("M" & r).Value Then Range("M" & r).Value = Range("L" & r).Value Range("O" & r).Value = Now End If If Range("L" & r).Value < Range("N" & r).Value Then Range("N" & r).Value = Range("L" & r).Value Range("P" & r).Value = Now End If Next r Application.EnableEvents = True Application.ScreenUpdating = True End SubSee the attached version.
Sir,
Thanks it is working. But finding 1 small problem.
Problem:-The value range is -1% to 1% in profit part. But the min value is always taking <=0,
1 small changes is requested.
Changes:-
If There is two different profit column
1 for Max profit and 1 for Min profit .
how the changes will be?
Please find attached for your reference
Thanks
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