Highest Lowest price from running price

[dhanu1660]

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 M10=100
. if L9 updated 110 then M10=110
. if L9 updated to 90 then M10=110
M10 should be update only for highest values not for lowest values.

[#2]

@samnpti 

What is the formula in column A?

[#3]

@Hans Vogelaar

sir,

It is taking reference from D

see the attachment

up to A1 to A8 manual typing working .And it clear cell in B

but From A9 it is reference cell from D9 .If change in D9 B9 should clear

[#4]

@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

[#5]

@Hans Vogelaar 

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.

[#6]

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

[#7]

M will changed by existing VBA code

[#8]

@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

[#9]

Sir its working perfectly

thanks again.

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

[#10]

@samnpti 

Please start a new discussion if you have a new, unrelated question.

[#11]

yes sir,

I have tagged you in new discussion.