Formula refining

hi after i posted my question here at the following link
http://www.excelforum.com/excel-prog...ml#post2328035
i did further research, this has made me realize i need to use a formula which is so simple, so here i am arriving at this conclusion but before i got further please take a look at the previous post and read the following as i have tried my very best to get my message across

I will give you case scenarios

CASE 1

A1=J1

B1=IF(A1<D1,D1,A1)

C1=IF(A1<D1,A1,D1)

D1=K1

VBA code: Nill

User Input Values (only in J1 and K1, which means in the actual fact you are changing the A1 and D1)

J1=15

K1=20

Result:

B1=20

C1=15



CASE 2 (Following the previous case values)

A1=J1=15

B1=IF(A1<D1,D1,A1)=20

C1=IF(A1<D1,A1,D1)=15

D1=K1=20

VBA code: Nill

User Input Values (only in J1 and K1, which means in the actual fact you are changing the A1 and D1)

J1=14

K1=20

Result:

B1=20

C1=14

CASE 3 (Following the previous case values)

A1=J1=14

B1=IF(A1<D1,D1,A1)=20

C1=IF(A1<D1,A1,D1)=14

D1=K1=20

VBA code: Nill

User Input Values (only in J1 and K1, which means in the actual fact you are changing the A1 and D1)

J1=21

K1=20

Result:

B1=21

C1=20

THIS IS WHAT I WOULD LIKE TO HIGHLIGHT WITH UTMOST CONCENTRATION

CASE (using my existing VBA code, which is at the bottom)

(Same values as previous but with different output)

A1=J1=14

B1=IF(A1<D1,D1,A1)=20

C1=IF(A1<D1,A1,D1)=14

D1=K1=20

VBA code: Nill

User Input Values (only in J1 and K1, which means in the actual fact you are changing the A1 and D1)

J1=21

K1=20

Result:

B1=21

C1=14 the low value retains there from the previous low value, unless a new low value lets say 13 is being input, this same theory applies to the highest value



See the highlighted values this is a very big clue, I am not sure how to get my message across even after this much explanation!!!

THE VBA CODE

Please Login or Register  to view this content.

I would like to either ensure that the formula is working properly or use a VBA code above but to alter the code to make sure that it can work with Calculate event, this is required as there cant be any target in calculate event