Highest & Lowest

[hsandeep]

Z1=1 or 2 as soon as system time equals 'start time' & 'end time' respectively else Z1=0 (default)
During this "Time Period" when Z1 first becomes equal to 1 & finally when it becomes equal to 2;
C2:C200 & D2 D200 keeps on updating its values continuously.
Output required: in E2:E200 & F2:F200 which should be the highest value of respective cell address of C column & the lowest value of respective cell address of D column AS LONG AS Z1=1.
Ex:
For Z1=1
C2=200.22 D2=507.43 E2=200.22 F2=507.43
C2=201.34 D2=501.43 E2=201.34 F2=501.43
C2=204.75 D2=500.43 E2=204.75 F2=500.43
C2=204.75 D2=508.55 E2=204.75 F2=500.43
C2=203.89 D2=500.43 E2=204.75 F2=500.43
...
Answer is E2 & F2.
Similarly for other C3:C200 & D3:D200 answer would be in E3:E200 & F3:F200.
How to accomplish?

 

[Andrew Poulsom]

So it's a formula?

 

[hsandeep]

YES in Z1

 

[Andrew Poulsom]

In that case you could put some VBA code in the Worksheet_Calculate event procedure, but it may slow things down.

 

[hsandeep]

I am novice in macros. Andrew, please help me with a proper code.

 

[Andrew Poulsom]

Have a try yourself and post back if you are still having problems. You have plenty of previous solutions to get you started. I agree with the comment by MARK858 at the end of Post #18 here:

http://www.mrexcel.com/forum/excel-questions/805308-help-needed-macro.html#post3937995

 

[hsandeep]

Sir, Appreciate once again..but finally today just now I had deleted that ws since I don't understand EXACTLY what is 'worksheet code event' & may be because of some silly error my Excel workbook was not working properly today & was causing enough problems in the broadband connectivity. After getting feedback for "Help needed for a macro" only; I would dare to re-insert the ws.

So, please try & understand my current situation & write the macros...would be grateful to you.

 

[Andrew Poulsom]

Weren't you given some examples of event code here?

http://www.mrexcel.com/forum/excel-questions/787312-can-formula-removed-excel.html

 

[hsandeep]

Weren't you given some examples of event code here?

http://www.mrexcel.com/forum/excel-questions/787312-can-formula-removed-excel.html

I learned pasting code(s) in vba thr' this from thread #2. I did the same actions for "Help needed for a macro" Excel workbook but the result did not came. ALSO I don't know whether I had done as per MARK858 or not i.e. had I pasted in 'worksheet code event' or 'standard module'?

 

[Andrew Poulsom]

If you are uncomfortable writing and maintaining VBA code I think you should avoid it, especially if your are going to be using the workbook for your "bread & butter".

 

[hsandeep]

I'll just say being very systematic & hard working, I CAN MAINTAIN the vba codes 'given to me' but I can't write. Using Excel for so many years I can VERY WELL MAINTAIN THE DRAFTED CODES & I had already created a huge Excel workbook which gives me my bread & butter but few more ws are needed with codes, drafting of which is not an easy task for me but I still say that I can maintain it very well. The code given by PETER SS is helping me extremely well & the result which I get in the cell(s) thr' codes are pre-thought for further defined action & results. So please help.