Recording a high price on a worksheet

[Guest]

I need to know the highest price I have ever paid for a product. In my
inventory, I update prices monthly, changing the previously recorded price.
If the "new price" is more than the price recorded in the "high price"
column, I want that price to change to the "new Price" amount. I have tried
using the MAX formula in conjunction with the IF formula, but it is not
working properly because if the "New Price" is in between the two prices, it
records that "new price". Any suggestions? Note: I am pretty much a novice
at this so please try to keep it simple.

 

[Ardus Petus]

You will need some VBA code.

Right-click on tab name,
Paste following code
Adjust Range("A:A") to suit your own "new price" column (eg: "G:G")
Close VBA

Et voilà!

I reckon it is not as simple as you could expect!

HTH
--
AP

'-----------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Dim myCell As Range
Set myRange = Intersect(Target, Range("A:A"))
If Not myRange Is Nothing Then
For Each myCell In myRange
With myCell
If .Value > .Offset(0, 1).Value Then _
.Offset(0, 1).Value = .Value
End With
Next myCell
End If

End Sub