Forum Discussion

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
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

Brass Contributor

Jul 19, 2021

M will changed by existing VBA code

HansVogelaar

MVP

Jul 19, 2021

samnpti

Private Sub Worksheet_Calculate()
    Dim r As Long
    Dim f As Boolean
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    For r = 9 To 133
        f = False
        If Range("K" & r).Value <> "" Then
            If Range("M" & r).Value = "" Then
                Range("M" & r).Value = Range("K" & r).Value
                If Range("M" & r).Value >= 0.01 Then
                    Range("N" & r).ClearContents
                    f = True
                End If
                Range("O" & r).Value = Now
            ElseIf Range("K" & r).Value > Range("M" & r).Value Then
                Range("M" & r).Value = Range("K" & r).Value
                If Range("M" & r).Value >= 0.01 Then
                    Range("N" & r).ClearContents
                    f = True
                End If
                Range("O" & r).Value = Now
           End If
        End If
        If Not f Then
            If Range("L" & r).Value <> "" Then
                If Range("N" & r).Value = "" Then
                    Range("N" & r).Value = Range("L" & r).Value
                    Range("P" & r).Value = Now
                ElseIf Range("L" & r).Value < Range("N" & r).Value Then
                    Range("N" & r).Value = Range("L" & r).Value
                    Range("P" & r).Value = Now
                End If
            End If
        End If
    Next r
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

samnpti
Brass Contributor
Jul 19, 2021
yes sir,

I have tagged you in new discussion.
HansVogelaar
MVP
Jul 19, 2021
samnpti

Please start a new discussion if you have a new, unrelated question.
samnpti
Brass Contributor
Jul 19, 2021
Sir its working perfectly

thanks again.

I have a different issue .I mean different topic. may i post it here?