# Totally stuck trying to format,loop etc...please help a novice!tha

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

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

Ad

P

#### Patrick Molloy

you can do this with conditional formatting

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

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

S

#### Sam

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

J

#### JLGWhiz

Is this homework?

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

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

S

#### SAM

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

Y

#### ytayta555

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

Ad

Y

#### ytayta555

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 .

S

#### SAM

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?

Y

#### ytayta555

Is better to explain here in the newsgroup what
you need , I'm a beginner too . I use 2003 version , can
look only to files saved in xls. format , my email is
(e-mail address removed) .

P

#### Patrick Molloy

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

J

#### JLGWhiz

See if you can work with this:

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

Not sure if I have the comparisons to Now in the right order, but you can
change those.

Ad

S

#### Sam

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

S

#### Sam

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

J

#### JLGWhiz

The value of x int the code I suggested does accumulate so long as the
condition is true and the value of x is posted in column G on each
iteration. If the condition is false or rather if the ElseIf Condition is
true, then the value of x becomes zero and that is posted to column G. I
just eliminated posting a value of 1 in a column each time as you had
illustrated. Maybe I just did not fully understand what you are doing.

J

#### JLGWhiz

I missed the Next part of the statement. Here is the revised code:

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
Next
End Sub

Now it should accumulate all of the differences.

S

#### Sam

can i send you an example of the sheet i am making....to your email?

Ad

J

#### JLGWhiz

No, I only try to assist through the news group. But if you can give clear
explanation of what the code does or does not do and what you need it to do,
I will do my best to help you.

S

#### Sam

i've put a fresh post up......i think it's much clearer and explains the
problems we're having....

Y

#### ytayta555

For your Step 2 , as I see , ker_01 brought the solution ,
the second very clever formula , =IF(B2*C1>0,B2+C1,B2) , :
http://groups.google.ro/group/micro...amming/browse_thread/thread/efdb0c6bddb4c1d8#

and , next code , maybe cover really all your needs :

Sub GIVEATRYFORSTEPTWO()

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

Set FromWbook = Workbooks("YYYYY.xls").Worksheets("YYYYY")

With FromWbook
Set myRng1 = .Range("G11:G71")
End With

For Each myCell In myRng1.Cells
If myCell.Value < 0 Then
myCell.Font.ColorIndex = 4
ElseIf myCell.Value > 0 Then
myCell.Font.ColorIndex = 3
Else
End If
If myCell.Value = 9 Then
myCell.Select
Selection.Font.Bold = True
Selection.Font.ColorIndex = 1
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
ElseIf myCell.Value = -9 Then
myCell.Select
Selection.Font.Bold = True
Selection.Font.ColorIndex = 1
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
Else
End If
Next myCell

End Sub

Ad

Y

#### ytayta555

To make it totaly in VBA , without worksheet
formula , we must be waiting for a Guru .....

Ad

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