My Worksheet range B2:B10 are dynamic cells & keeps on getting updated continuously on real time basis as long as the Workbook remains opened.
Cell A1 which contains formula generates 1 or 0 & just dictates whether the values in Column E (E2:E10); Column F (F2:F10); Column G (G2:G10); Column H (H2:H10) & Column I (I2:I10) should continue to update or be frozen to whatever values they currently are with the rule that If A1=1, columns E; F; G; H & I should continue to update & if A1=0, columns E; F; G; H & I should stop updating and be frozen to whatever values they are.
I need vba to execute the following actions:
- Column E (E2:E10) should ‘grab’ MAXIMUM value generated in B2:B10 when A1=1
- Column F (F2:F10) should ‘grab’ MINIMUM value generated in B2:B10 when A1=1
- Column G (G2:G10) should get the FIRST OCCURRENCE value generated in B2:B10 when A1=1
- Column H (H2:H10) should get the LARGEST NUMBER THAT IS SMALLER THAN THE MAXIMUM NUMBER generated in B2:B10 when A1=1
- Column I (I2:I10) should get the SMALLEST NUMBER THAT IS HIGHER THAN THE MINIMUM NUMBER generated in B2:B10 when A1=1
- Reset columns E; F; G; H & I to null (“”) when A2=444 (A2 contains formula & generates either of 333 or 444)
Code should execute the above actions speedily.
Any help is greatly appreciated.