I have attached this code to the bottom of the former one so the while thing
looks like:

Sub GIVEATRY()

Dim FromWbook As Worksheet
Dim myCell As Range
Dim myRng1 As Range

Set FromWbook = Workbooks("1.xls").Worksheets("Sheet1")

With FromWbook
Set myRng1 = .Range("E11:E71")
End With

For Each myCell In myRng1.Cells
If myCell.Value = myCell.Offset(4, 0) Then
myCell.Offset(0, 1).Value = 1
myCell.Offset(0, 1).Select
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
ElseIf myCell.Value <= myCell.Offset(4, 0) Then
myCell.Offset(0, 1).Value = -1
myCell.Offset(0, 1).Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Else
End If
Next myCell

End Sub

Sub SIST()
Dim rng As Range, x As Long, c As Range

Set rng = ActiveSheet.Range("E11:E71")
For Each c In rng
If c.Value <= Now Then
x = x + 1
c.Offset(0, 2) = x
c.Offset(0, 2).Interior.ColorIndex = 10
ElseIf c.Value Now Then
x = 0
c.Offset(0, 2) = x
c.Offset(0, 2).Interior.ColorIndex = 3
End If
End Sub


Problem is this is not really doing what i need. Firstly i need the function
to sum up the points as it moves down the count (see descrition below) on the
methodology.
Right now it is only doing step 1....i need to get it to count the number
cumulatively (as i describe in step 2)



---------------------------
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. font colour: green background nothing

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. font colour: red background: nothing


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


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