Totally stuck trying to format,loop etc...please help a novice
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...
|