Forum Discussion

dhanu1660's avatar
dhanu1660
Copper Contributor
Jun 01, 2021
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...
excel
  • HansVogelaar's avatar
    HansVogelaar
    Jun 01, 2021

    dhanu1660 

    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 Sub

    Switch back to Excel.

    Save the workbook as a macro-enabled workbook.

    Make sure that you allow macros when you open it.

samnpti's avatar
samnpti
Brass Contributor
Jul 14, 2021
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:O19

HansVogelaar's avatar
HansVogelaar
MVP
Jul 14, 2021

samnpti 

What does the worksheet look like now?

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Jul 14, 2021

      samnpti 

      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
      • samnpti's avatar
        samnpti
        Brass Contributor
        Jul 14, 2021

        HansVogelaar 

         

        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.

Resources