Capture Highest & Lowest Value

[hsandeep]

B2 gets updated by values due RTD
B3 I have kept a flag which becomes 1 for a certain time period (ONLY ONCE DURING A DAY)
Solution required:
B4=HIGHEST VALUE of B2 when B3=1 else 0
B5= LOWEST VALUE of B2 when B3=1 else 0
How to accomplish?
Thanks in adv

 

[hsandeep]

Someone from this forum is requested to give me vba code

you can adjust by adding helper columns or rows to achieve the desired result

 

[hsandeep]

To get the solution I'll Add 1 more constraint that B2 will get updated for a maximum 30000 values when B3=1

 

[hsandeep]

I have tried using the below code....but 'not satisfactory' Can someone help further?

At the top of the code window, I used the two drop downs select Worksheet (in order to tell it I want a worksheet event) and then Change (to tell it I want the Worksheet_Change event. It auto created a line with Private Sub Worksheet_Change(ByVal Target as Range) and looked something like this:








Dim rDynamic As Range
Dim rMax As Range
Dim rMin As Range


Set rDynamic = Me.Range("B2")
Set rMax = Me.Range("E2")
Set rMin = Me.Range("F2")


If Not Intersect(Target, rDynamic) Is Nothing Then
If rDynamic.Value < rMin.Value Then rMin.Value = rDynamic.Value
If rDynamic.Value > rMax.Value Then rMax.Value = rDynamic.Value
End If

 

[hsandeep]

Can someone look into my code and tell me what mistake I am making