Selected Answer
When you start the attached workbook you will be offered the choice between "Yes", "No" and "Cancel".
- If you press "Cancel" the workbook will open normally, without any action.
- If you press "No", the Timer will be started.
- If you press "Yes", the previous Min & Max will be deleted and the Timer will be started.
You can also start the Timer manually by running the procedure "SetTimer". You can stop the Timer by runing the procedure "StopTimer". When you close the workbook the Timer will also be stopped.
The code that does all the above is contained in the two code modules ThisWorkbook and TXL_5303. The latter is a standard code module whose name doesn't matter for the functioning of the code. But if you place the code from ThisWorkbook in another type of module the project won't work as described here.
The Timer will run the code every minute. You can adjust the interval in the procedure SetTimer. The code will take the current value of D5:D7 and change E5:F7 if the new average is either lower or higher than the previous high or low.
In the sub MyMacro you can enable the workbook to be saved after each update. If you don't you will be asked whether you want to save changes when the workbook is closed.
In the Enum Nws, as the top of the code, you can set the position of the columns. You can change the columns almost as you wish but the Min must be to the right of Max.
I noticed that you didn't set the cell format for E6:E7. Therefore you will see the result of calculations in the wrong format. Read all the comments in the code and ask if there are any you don't understand.
18 Jun 2021 - EDIT: A bug in procedure MyMacro was fixed.