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 a column of data: example below:

COLUMN A COLUMN B
Last Price
1.64500
1.64520
1.64520
1.64540
1.64550
1.64550
1.64520
1.64570
1.64530
1.64510
1.64540
1.64550
1.64540
1.64540
1.64530
1.64500
1.64520
1.64560
1.64540
1.64580
1.64590
1.64570
1.64560
1.64550
1.64540
1.64520
1.64580
1.64580
1.64570
1.64600
1.64580
1.64600
1.64640
1.64650
1.64630
1.64650
1.64710
1.64710
1.64680
1.64690
1.64660
1.64650
1.64630
1.64640
1.64620
1.64640
1.64630
1.64620
1.64650
1.64650
1.64660
1.64640
1.64630
1.64640
1.64670
1.64690
1.64720
1.64720
1.64680
1.64680
1.64700



In "Column B" i want to do the following:

Step 1:

If the value in cell A2 is greater than or equal to the value in cell A5
assign value = 1
If the value in cell A2 is less than or equal to the value in cell A5 assign
value = -1

*at this point I realise that there is an illogical step (if the value is
the same, value according to the statement above will be equal to both 1 and
-1) step 2 explains what to do in this situation.

Step 2:

So we are currently calulating the relative 1's and -1s as we descend the
column.
I now need to sum these cumulatively. See 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,-1,-1,1,1,) it just starts the cumulative count again at zero
(showing 1,2,3,4,-1,-2,-3,1,2). So:

Example 1: All positive 1s€�
(count) I would want the number to add to each other each time
(sum cumulatively)
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9

Example 2: All negative -1s€�
(count) I would want the number to add to each other each time
(sum cumulatively)
-1 -1
-1 -2
-1 -3
-1 -4
-1 -5
-1 -6
-1 -7
-1 -8
-1 -9

Example 3: IMPORTANT: IF POSITIVE AND NEGATIVE 1/-1s
(count) In this situation the sum stops when the count changes
type (from -1 to 1, or 1 to -1) BUT the next count is EQUAL to the last one
the count continues. See example 4.
1 1
1 2
1 3
-1 -1
-1 -2
1 1
1 2
-1 -1
1 1

Example 4: I have included some of the relative movements that the 1s and
-1s are built off.
Raw data (count) I would want the number to add to each
other each time (sum cumulatively)
1.64500 1 1
1.64520 -1 -1
1.64520 -1 -2
1.64540 -1 -3 notice here that the two raw data values are EQUAL but we
still assign -1 and then add it in because it is part of the count. ONLY if
it were +1 would we stop the count.
1.64550 -1 -4
1.64550 1 1
1.64540 (changed to 40 just to show what I mean)
1.64570
1.64530

So what i would want the beginning bit of "Column B" to look like (I cant
do it all manually)

Column A Column B
Raw data count sum count
1.64500 1 1
1.64520 -1 -1
1.64520 -1 -2
1.64540 1 1
1.64550 -1 -1
1.64550 1 1
1.64520 1 2
1.64570 1 3
1.64530 -1 -1
1.64510 -1 -2
1.64540 -1 -3
1.64550 1 1
1.64540 n/a n/a
1.64540 n/a n/a
1.64530 n/a n/a

Lastly,

If the count sums are negative, font = green, background = clear/normal
If the count sums are positive, font = red, background = clear/normal
If the count gets to + 9, cell = red, font = black bold
If the count gets to - 9, cell = green, font = black bold

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


Thank you to anyone who can help me on this...