Highest Lowest price from running price

samnpti 

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

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 

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

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.

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

HansVogelaar

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

samnpti 

What is the formula in column A?

HansVogelaar 

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target >= 1 / 100 Then
Range("B" & Target.Row).Clear
End If
End If
End Sub

Sir how to change it to formula in "A"

only manual typing is clearing cell B.

want to clear B when A cell change from other reference cell.

something like below

Option Explicit
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target >= 1 / 100 Then
Range("B" & Target.Row).Clear
Application.EnableEvents = True
End Sub

but its not working

samnpti 

I'd leave it as it is now.

samnpti 

How would that work? Once you have a Min value you would never be able to set the Max value anymore, since that would make the Max timestamp later than the Min timestamp.