Recording a high value from another cell

[hammer2]

I have a spreadsheet that is automatically updated from another program I use.

Cell AD5 has a value that constantly changes (up and down) after each update from the program.

What I want to be able to do if it is possible, is to have another cell (AF5) show the HIGHEST figure ever recorded in cell AD5...and adjust accordingly each time AD5 hits it's highest ever amount.

eg, lets say AD5 starts with 100....it then drops to 98....AF5 retains 100.....AD5 then changes to 102....AF5 changes to 102....AD5 drops to 96....AF5 would retain 102...etc

The figure returned in cell AF5 is then used as a trigger in another column/cell.

I could use additional cells apart from AF5 if it is necessary.

Possible?

Many thanks for taking the challenge.
Cheers
Hammer

[hammer2]

Not possible?

[Andrew-R]

You'll need a little bit of macro code in the sheet change event for the sheet that gets updated...

Private Sub Worksheet_Change(ByVal Target As Range)

Const sSOURCE_RANGE = "AF5"
Const sTARGET_RANGE = "A1"

Application.EnableEvents = False

If Not Application.Intersect(Target, Range(sSOURCE_RANGE)) Is Nothing Then
  If Range(sSOURCE_RANGE).Value > Range(sTARGET_RANGE).Value Then
    Range(sTARGET_RANGE).Value = Range(sSOURCE_RANGE).Value
  End If
End If

Application.EnableEvents = True

End Sub

[hammer2]

Hi Andrew

Many thanks for your reply.

I should have mentioned that I am a certified dummy with these types of problems.

However, from reading other threads here I left clicked on the worksheet and pasted in the above code.

Nothing appears to happen though in cell AF5.

No doubt I need the step by step dummies guide to achieve a result.

Cell A1 by the way has other info in it from the program I use if that in any way helps.

Thanks again

Hammer

[Andrew-R]

You can use any cell you like to record the high value, just change the sTARGET_RANGE cell to whichever cell you want to use, so if say you wanted the high value recorded in C5 you'd change the 3rd line of code to:

Const sTARGET_RANGE = "C5"

OK, to make it work:

1. Go into the VB Editor (Alt-F11)
2. In the left-hand pane double click on the sheet which is going to be updated.
3. From the left-hand drop-down list above the text editor select "Worksheet"
4. From the right-hand drop down select "Change"
5. Excel will put in the first and last lines of my code automatically, so you'll have a screen which looks like:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

6. Paste in the code I gave you above - you won't need to paste the first and last lines, because they're already there.

7. That should be it.

Let me know if it works for you.

[hammer2]

Andrew, pure genius!!!
Thank you so much for your time and effort, not to mention patience

What a great site this is.