Hi there,



You'll have to forgive me because i am a bit of a novice wiith respect to
vba but i am trying to learn it...rather painfully.



So here's the problem:



I have two columns of data: example below:



Period Last Price "Column C" "Column D"

22/07/2009 08:54 1.63360

22/07/2009 08:53 1.63370

22/07/2009 08:52 1.63330

22/07/2009 08:51 1.63310

22/07/2009 08:50 1.63300

22/07/2009 08:49 1.63310

22/07/2009 08:48 1.63300

22/07/2009 08:47 1.63290

22/07/2009 08:46 1.63250

22/07/2009 08:45 1.63290

22/07/2009 08:44 1.63230

22/07/2009 08:43 1.63200



In "Column C" i want to print the following rules:



Step 1:



set-up 1:

If the value at time now (in this case: 08:54, value 1.63360) is greater
than or equal to the value at time minus 4 cells earlier (in this case 08:51,
value 1.63360) assign value = 1. colour: green



set-up 2:

If the value at time now (in this case: 08:54, value 1.63360) is less than
or equal to the value at time minus 4 cells earlier (in this case 08:51,
value 1.63360) assign value = -1. colour: red





Step 2:



So we are currently calulating the relative +1's and -1's. I need to
cumulatively sum each of these according to the example below:



If i have 1,1,1,1,1 the formula needs to sum them as it "counts" (showing
1, 2, 3, 4, 5, respectively). If it is ever interupted (e.g. 1,1,1,1,-1) it
just starts the cumulative count again at zero (showing 1,2,3,4,0). So:



count would look like:

1 1

1 2

1 3

1 4

1 5

1 6

-1 0

1 1

1 2



when the count get's to +9 the cell should go red and the font black.

when the count gets to -9 the cell should go green and the font black.



finshed.



So what i want is for "Column C" to look like:



Period Last Price Column C

22/07/2009 08:54 1.63360 1 (with red font)

22/07/2009 08:53 1.63370 2 (with red font)

22/07/2009 08:52 1.63330 3 (with red font)

22/07/2009 08:51 1.63310 4 (with red font)

22/07/2009 08:50 1.63300 5 (with red font)

22/07/2009 08:49 1.63310 6 (with red font)

22/07/2009 08:48 1.63300 7 (with red font)

22/07/2009 08:47 1.63290 8 (with red font)

22/07/2009 08:46 1.63250 9 (with bold black font, red
square)

22/07/2009 08:45 1.63290 1 (with red font)

22/07/2009 08:44 1.63230 -1 (with green font)

22/07/2009 08:43 1.63200 -2 (with green font)



basically i am totally stuck....i have tried writing some loops but mine
totally fail all the time....it's so so annoying. Sorry to ask for so much
help but i have been painfully staring at vba for dummies for the last week
and am stuck....

see your earlier thread

"SAM" wrote in message
...
Hi there,



You'll have to forgive me because i am a bit of a novice wiith respect to
vba but i am trying to learn it...rather painfully.



So here's the problem:



I have two columns of data: example below:



Period Last Price "Column C" "Column D"

22/07/2009 08:54 1.63360

22/07/2009 08:53 1.63370

22/07/2009 08:52 1.63330

22/07/2009 08:51 1.63310

22/07/2009 08:50 1.63300

22/07/2009 08:49 1.63310

22/07/2009 08:48 1.63300

22/07/2009 08:47 1.63290

22/07/2009 08:46 1.63250

22/07/2009 08:45 1.63290

22/07/2009 08:44 1.63230

22/07/2009 08:43 1.63200



In "Column C" i want to print the following rules:



Step 1:



set-up 1:

If the value at time now (in this case: 08:54, value 1.63360) is greater
than or equal to the value at time minus 4 cells earlier (in this case
08:51,
value 1.63360) assign value = 1. colour: green



set-up 2:

If the value at time now (in this case: 08:54, value 1.63360) is less than
or equal to the value at time minus 4 cells earlier (in this case 08:51,
value 1.63360) assign value = -1. colour: red





Step 2:



So we are currently calulating the relative +1's and -1's. I need to
cumulatively sum each of these according to the example below:



If i have 1,1,1,1,1 the formula needs to sum them as it "counts"
(showing
1, 2, 3, 4, 5, respectively). If it is ever interupted (e.g. 1,1,1,1,-1)
it
just starts the cumulative count again at zero (showing 1,2,3,4,0). So:



count would look like:

1 1

1 2

1 3

1 4

1 5

1 6

-1 0

1 1

1 2



when the count get's to +9 the cell should go red and the font black.

when the count gets to -9 the cell should go green and the font black.



finshed.



So what i want is for "Column C" to look like:



Period Last Price Column C

22/07/2009 08:54 1.63360 1 (with red font)

22/07/2009 08:53 1.63370 2 (with red font)

22/07/2009 08:52 1.63330 3 (with red font)

22/07/2009 08:51 1.63310 4 (with red font)

22/07/2009 08:50 1.63300 5 (with red font)

22/07/2009 08:49 1.63310 6 (with red font)

22/07/2009 08:48 1.63300 7 (with red font)

22/07/2009 08:47 1.63290 8 (with red font)

22/07/2009 08:46 1.63250 9 (with bold black font, red
square)

22/07/2009 08:45 1.63290 1 (with red font)

22/07/2009 08:44 1.63230 -1 (with green font)

22/07/2009 08:43 1.63200 -2 (with green font)



basically i am totally stuck....i have tried writing some loops but mine
totally fail all the time....it's so so annoying. Sorry to ask for so much
help but i have been painfully staring at vba for dummies for the last
week
and am stuck....