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


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

you can do this with conditional formatting

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


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

thing is i want to be able to do it using vba...so it's cleaner.

"Patrick Molloy" wrote:

you can do this with conditional formatting

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


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

this should get you started anyway:

Option Explicit

Sub update_C()
Dim target As Range
Dim runtime As Double
Dim targettime As Double

'runtime = Now - TimeValue("00:04:00")
runtime = DateValue("22/07/2009") + TimeValue("08:51:00")
Set target = Range("C2")
Do Until target = ""
targettime = DateValue(target.Value) + TimeValue(target.Value)
If targettime = runtime Then
'green
target.Offset(, 2) = 1
Else
' red
target.Offset(, 2) = -1
End If
Set target = target.Offset(1)
Loop
End Sub

TODO: aggregate the values
and add the formatting

"Sam" wrote in message
...
thing is i want to be able to do it using vba...so it's cleaner.

"Patrick Molloy" wrote:

you can do this with conditional formatting

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


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

Hi Patrick - i took a look at the code....

but the thing is the time element changes....so it has to look down the
range all the time....

"Patrick Molloy" wrote:

this should get you started anyway:

Option Explicit

Sub update_C()
Dim target As Range
Dim runtime As Double
Dim targettime As Double

'runtime = Now - TimeValue("00:04:00")
runtime = DateValue("22/07/2009") + TimeValue("08:51:00")
Set target = Range("C2")
Do Until target = ""
targettime = DateValue(target.Value) + TimeValue(target.Value)
If targettime = runtime Then
'green
target.Offset(, 2) = 1
Else
' red
target.Offset(, 2) = -1
End If
Set target = target.Offset(1)
Loop
End Sub

TODO: aggregate the values
and add the formatting

"Sam" wrote in message
...
thing is i want to be able to do it using vba...so it's cleaner.

"Patrick Molloy" wrote:

you can do this with conditional formatting

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


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

Is this homework?



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


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

ha ha ha...no. I am 25! although if i was 18 and at uni, you're right...this
could be a useful site.


"JLGWhiz" wrote:

Is this homework?



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


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

On 22 Iul, 19:47, SAM wrote:
Step 1: set-up 1: If the value ......

For your Step 1 , work with this , and make ajustments
according with your needs to workbook and worksheet
name .., the lenght of your range where you have Last
Price ....

Sub GIVEATRY()

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

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

With FromWbook
Set myRng1 = .Range("B2:B20")
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

Hope to help

On 22 Iul, 20:26, ytayta555 wrote:

Something in your Step 1 is not logical , if the value is
< or = , or , or = , in the case the value is
equal ,
you always shall have the color red or green ( not shure ) .
Maybe you must put one more condition , such as , if value is
equal to the value at time minus 4 cells earlier , then ,
value to be "x" and color ... how you want .

my actual data is in cell e11:e71..if the next value on is a continuation of
the series (either +1, or -1) i want it to know to label the 0 as a
continuation of the series...?

do you have an email?

"ytayta555" wrote:

On 22 Iul, 20:26, ytayta555 wrote:

Something in your Step 1 is not logical , if the value is
< or = , or , or = , in the case the value is
equal ,
you always shall have the color red or green ( not shure ) .
Maybe you must put one more condition , such as , if value is
equal to the value at time minus 4 cells earlier , then ,
value to be "x" and color ... how you want .