Changing cell colors base on previous cell values

[Josephoo]

Hi

I have been researching this for a while but still can't seem to get my head around this. I have an excel sheet that does the following:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]P1 + P2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]P1[/TD]
[TD]P2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]70[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]139[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]70[/TD]
[TD]69[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]139[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]89[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

P1 and P2 have a data set that is linked to a third party app in excel. the values changes by the seconds or minutes. So E2:F4 is a dataset of changing values.

A2 is a formula that say '=E2+F2'. This is the same for A3('=E3+F3') and A4('=E4+F4') etc. I'm trying to change cell.interior.colorindex of A2 base on previous value. so example if at T0 A2 is 70 and T+1 A2 is 69. Then cell of A2 will be red. if T0 A2 is 70 and T+1 A2 is 73, A2 will change color to green. and it resets to no colors if the values say for E2 and F2 changes but T0 A2 = T+1 A2. it is smth similar to the stock board ticker where u can see green and red moving up and down. How do i account if i have a range of data say A1:D100? i dont suppose i code each and individual cell because that would just be very heavy in terms of calculations.

Any help would be appreciated. i know i have to use VBA for this as conditional formatting doesn't work. is there a way to check using arrays? i have limited programming background on this and is not too sure how i can use array to store the previous value to counter check and apply the color changes.

Thanks.

Regards,
Joseph

 

[Josephoo]

Any help would be much appreciated.