Find Max in changing data

[kanga85]

In a excel sheet I have in column A a list of items, in column B a list of corresponding values. These values are updated daily over the course of a year. In column C I would like a list of the max value listed in column B over the course of time.

I have tried putting the current MAX value into a dummy column D, and then trying to put the MAX of B and D into C, but run into circular problems.

Should be easy, and any help would be appreciated.

Thanks

 

[footoo]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cel As Range
Set rng = Intersect(Target, [B:B])
If Not rng Is Nothing Then
    Application.EnableEvents = False
    For Each cel In rng
        If cel(1, 2) < cel Then cel(1, 2) = cel.Value
    Next
    Application.EnableEvents = True
End If
End Sub

 

[kanga85]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cel As Range
Set rng = Intersect(Target, [B:B])
If Not rng Is Nothing Then
    Application.EnableEvents = False
    For Each cel In rng
        If cel(1, 2) < cel Then cel(1, 2) = cel.Value
    Next
    Application.EnableEvents = True
End If
End Sub

Thanks for your rapid reply. But I was wondering whether this could be done without a macro.

 

[footoo]

Thanks for your rapid reply. But I was wondering whether this could be done without a macro.

Try this :

C1 : =IF(B1>D1,B1,D1)

D1 : =MAX(B1:C1)

You will have to enable iterative calculations in Excel options.

 

[kanga85]

Thanks footoo

 

[kanga85]

Thanks footoo

I had this years ago in my program, and an Excel update caused it not to work. I had forgotten the requirement to 'enable iterative calculations'. Works well now!