# Totally stuck trying to format,loop etc...re-post.please help

S

#### SAM

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....

Ad

P

#### Patrick Molloy

see your earlier thread

SAM said:
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....

## Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.