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.

HansVogelaar

MVP

Jul 19, 2021

samnpti

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Not Intersect(Range("A:A"), Target) Is Nothing Then
        Application.EnableEvents = False
        For Each rng In Intersect(Range("A:A"), Target)
            If rng.Value >= 0.01 Then
                rng.Offset(0, 1).ClearContents
            End If
        Next rng
        Application.EnableEvents = True
    End If
    If Not Intersect(Range("D:D"), Target) Is Nothing Then
        Application.EnableEvents = False
        For Each rng In Intersect(Range("D:D"), Target)
            If rng.Offset(0, -3).Value >= 0.01 Then
                rng.Offset(0, -2).ClearContents
            End If
        Next rng
        Application.EnableEvents = True
    End If
End Sub

samnpti

Brass Contributor

Jul 19, 2021

HansVogelaar

Sir,

thank you for your code. But it is not working in my case.

Please find the attached.

My requirement is whenever M value will >1% it will clear N corresponding cell only once.

Hans sir.xls71 KB

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?

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
M will changed by existing VBA code
HansVogelaar
MVP
Jul 19, 2021
samnpti

Will the values in column M be entered manually, or are they the result of a formula, or are they changed by VBA code?