run sub routine with multiple conditions
 

Yes, rows should be formatted blue only if all the related conditions for all
selected checkboxes are true. And if any related condition is false, then no
row should be formatted blue. You are correct, its all or nothing,
Here a description of what I am trying to do:
I have table of data where the first row represents the stock data of the day
and other calculated values. Each subsequent row represents another day
with its calculated values. So this table then has many rows and 16
columns of data.
I will then, using checkboxes choose a combination of several different
conditions for testing.
The program will loop through the first row and tests wether the combination
of all the different conditions on this particular day are true. If so, this
row
will be colored blue.
A typical condition would be: is todays volume higher than yesterdays?
ie. (if cells(10 + i,6)cells(9 + i,6) Then).And there would be several more
of this kind

It will then move to the next row and repeat the test with those same
conditions
until it reaches the end of the table. I will then have a table where some
rows are
colored blue because they satisfied all selected conditions.
The purpose of this is to use the most promising combinations of conditions
for
forecasting bullish or bearish stock trends. It is a quick way to check out
your stock
system.
I accidentally copied the wrong €œCase€� lines previously, the code below is
the
correct one. I apologize for this and for any other confusion I caused
earlier.
Do you think this project can be done ?

Private Sub CommandButton1_Click()
Dim ctl As Control
Dim bolCheck As Boolean
Dim i As Integer
Dim wks As Worksheet

Set wks = Worksheets("day1")

bolCheck = True
For i = 1 To 23
Set ctl = UserForm1.Controls("Checkbox" & i)
If ctl = True Then
'This checkbox is selected. Is its related condition True?
Select Case i

Case 1
If Not wks.Cells(10 + i, 3).Value wks.Cells(9 + i, 3).Value
Then bolCheck = False 'spread
Case 2
If Not wks.Cells(10 + i, 3).Value < wks.Cells(9 + i, 3).Value
Then bolCheck = False 'vol
Case 3
If Not wks.Cells(10 + i, 4).Value wks.Cells(9 + i, 4).Value
Then bolCheck = False
Case 4
If Not wks.Cells(10 + i, 4).Value < wks.Cells(9 + i, 4).Value
Then bolCheck = False
Case 5
If Not wks.Cells(10 + i, 5).Value wks.Cells(9 + i, 5).Value
Then bolCheck = False
Case 6
If Not wks.Cells(10 + i, 5).Value < wks.Cells(9 + i, 5).Value
Then bolCheck = False
Case 7
If Not wks.Cells(10 + i, 6).Value wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 8
If Not wks.Cells(10 + i, 6).Value < wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 9
If Not wks.Cells(10 + i, 7).Value wks.Cells(9 + i, 7).Value
Then bolCheck = False
Case 10
If Not wks.Cells(10 + i, 7).Value < wks.Cells(9 + i, 7).Value
Then bolCheck = False
Case 11
If Not wks.Cells(10 + i, 8).Value wks.Cells(9 + i, 8).Value
Then bolCheck = False
Case 12
If Not wks.Cells(10 + i, 8).Value < wks.Cells(9 + i, 8).Value
Then bolCheck = False
Case 13
If Not wks.Cells(10 + i, 9).Value wks.Cells(9 + i, 9).Value
Then bolCheck = False
Case 14
If Not wks.Cells(10 + i, 9).Value < wks.Cells(9 + i, 9).Value
Then bolCheck = False
Case 15
If Not wks.Cells(10 + i, 10).Value wks.Cells(9 + i, 10).
Value Then bolCheck = False
Case 16
If Not wks.Cells(10 + i, 10).Value < wks.Cells(9 + i, 10).
Value Then bolCheck = False
Case 17
If Not wks.Cells(10 + i, 11).Value wks.Cells(9 + i, 11).
Value Then bolCheck = False
Case 18
If Not wks.Cells(10 + i, 11).Value < wks.Cells(9 + i, 11).
Value Then bolCheck = False
Case 19
If Not wks.Cells(10 + i, 12).Value wks.Cells(9 + i, 12).
Value Then bolCheck = False
Case 20
If Not wks.Cells(10 + i, 12).Value < wks.Cells(9 + i, 12).
Value Then bolCheck = False
Case 21
If Not wks.Cells(10 + i, 13).Value wks.Cells(9 + i, 13).
Value Then bolCheck = False
Case 22
If Not wks.Cells(10 + i, 13).Value < wks.Cells(9 + i, 13).
Value Then bolCheck = False
Case 23
If Not wks.Cells(10 + i, 14).Value wks.Cells(9 + i, 14).
Value Then bolCheck = False

End Select
End If
If bolCheck = False Then
'(Optional)
'A related condition is False. No need to continue checking any more
'boxes or conditions.
Exit For
End If
Next i

If bolCheck = True Then
'Do whatever it is you want to do if all conditions related to the
'checked boxes are True
'Call BlueRow
blue
End If

End Sub

Sub BlueRow()
' color rows blue
Dim i As Integer
Dim wks As Worksheet

Set wks = Worksheets("day1")

For i = 1 To 23
If UserForm1.Controls("Checkbox" & i) = True Then
wks.Range(Cells(10 + i, 2), wks.Cells(10 + i, 16)).Interior.
ColorIndex = 5
End If
Next i
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200802/1

run sub routine with multiple conditions
 

OK. Sounds like you only want to evaluate ONE row at a time? Check that ONE
row against all selected conditions and turn it blue if all conditions are
true? Only then do we move on to the next row and do the same, etc. That's
fine, but different from my original understanding (which was that all
condition have to be true for all rows for anything to change color. I was
applying "all or nothing" to the entire table, not a single row.). It will
require some code changes.

What is the starting row? What determines end-of-table? Currently we are
using the same variable to loop through both checkboxes and rows. We have to
change that.

--
HTH,
George


"hans via OfficeKB.com" <u39929@uwe wrote in message
news:80067c559fefc@uwe...
Yes, rows should be formatted blue only if all the related conditions for
all
selected checkboxes are true. And if any related condition is false, then
no
row should be formatted blue. You are correct, its all or nothing,
Here a description of what I am trying to do:
I have table of data where the first row represents the stock data of the
day
and other calculated values. Each subsequent row represents another day
with its calculated values. So this table then has many rows and 16
columns of data.
I will then, using checkboxes choose a combination of several different
conditions for testing.
The program will loop through the first row and tests wether the
combination
of all the different conditions on this particular day are true. If so,
this
row
will be colored blue.
A typical condition would be: is todays volume higher than yesterdays?
ie. (if cells(10 + i,6)cells(9 + i,6) Then).And there would be several
more
of this kind

It will then move to the next row and repeat the test with those same
conditions
until it reaches the end of the table. I will then have a table where some
rows are
colored blue because they satisfied all selected conditions.
The purpose of this is to use the most promising combinations of
conditions
for
forecasting bullish or bearish stock trends. It is a quick way to check
out
your stock
system.
I accidentally copied the wrong "Case" lines previously, the code below is
the
correct one. I apologize for this and for any other confusion I caused
earlier.
Do you think this project can be done ?

Private Sub CommandButton1_Click()
Dim ctl As Control
Dim bolCheck As Boolean
Dim i As Integer
Dim wks As Worksheet

Set wks = Worksheets("day1")

bolCheck = True
For i = 1 To 23
Set ctl = UserForm1.Controls("Checkbox" & i)
If ctl = True Then
'This checkbox is selected. Is its related condition True?
Select Case i

Case 1
If Not wks.Cells(10 + i, 3).Value wks.Cells(9 + i,
3).Value
Then bolCheck = False 'spread
Case 2
If Not wks.Cells(10 + i, 3).Value < wks.Cells(9 + i,
3).Value
Then bolCheck = False 'vol
Case 3
If Not wks.Cells(10 + i, 4).Value wks.Cells(9 + i,
4).Value
Then bolCheck = False
Case 4
If Not wks.Cells(10 + i, 4).Value < wks.Cells(9 + i,
4).Value
Then bolCheck = False
Case 5
If Not wks.Cells(10 + i, 5).Value wks.Cells(9 + i,
5).Value
Then bolCheck = False
Case 6
If Not wks.Cells(10 + i, 5).Value < wks.Cells(9 + i,
5).Value
Then bolCheck = False
Case 7
If Not wks.Cells(10 + i, 6).Value wks.Cells(9 + i,
6).Value
Then bolCheck = False
Case 8
If Not wks.Cells(10 + i, 6).Value < wks.Cells(9 + i,
6).Value
Then bolCheck = False
Case 9
If Not wks.Cells(10 + i, 7).Value wks.Cells(9 + i,
7).Value
Then bolCheck = False
Case 10
If Not wks.Cells(10 + i, 7).Value < wks.Cells(9 + i,
7).Value
Then bolCheck = False
Case 11
If Not wks.Cells(10 + i, 8).Value wks.Cells(9 + i,
8).Value
Then bolCheck = False
Case 12
If Not wks.Cells(10 + i, 8).Value < wks.Cells(9 + i,
8).Value
Then bolCheck = False
Case 13
If Not wks.Cells(10 + i, 9).Value wks.Cells(9 + i,
9).Value
Then bolCheck = False
Case 14
If Not wks.Cells(10 + i, 9).Value < wks.Cells(9 + i,
9).Value
Then bolCheck = False
Case 15
If Not wks.Cells(10 + i, 10).Value wks.Cells(9 + i, 10).
Value Then bolCheck = False
Case 16
If Not wks.Cells(10 + i, 10).Value < wks.Cells(9 + i, 10).
Value Then bolCheck = False
Case 17
If Not wks.Cells(10 + i, 11).Value wks.Cells(9 + i, 11).
Value Then bolCheck = False
Case 18
If Not wks.Cells(10 + i, 11).Value < wks.Cells(9 + i, 11).
Value Then bolCheck = False
Case 19
If Not wks.Cells(10 + i, 12).Value wks.Cells(9 + i, 12).
Value Then bolCheck = False
Case 20
If Not wks.Cells(10 + i, 12).Value < wks.Cells(9 + i, 12).
Value Then bolCheck = False
Case 21
If Not wks.Cells(10 + i, 13).Value wks.Cells(9 + i, 13).
Value Then bolCheck = False
Case 22
If Not wks.Cells(10 + i, 13).Value < wks.Cells(9 + i, 13).
Value Then bolCheck = False
Case 23
If Not wks.Cells(10 + i, 14).Value wks.Cells(9 + i, 14).
Value Then bolCheck = False

End Select
End If
If bolCheck = False Then
'(Optional)
'A related condition is False. No need to continue checking any more
'boxes or conditions.
Exit For
End If
Next i

If bolCheck = True Then
'Do whatever it is you want to do if all conditions related to the
'checked boxes are True
'Call BlueRow
blue
End If

End Sub

Sub BlueRow()
' color rows blue
Dim i As Integer
Dim wks As Worksheet

Set wks = Worksheets("day1")

For i = 1 To 23
If UserForm1.Controls("Checkbox" & i) = True Then
wks.Range(Cells(10 + i, 2), wks.Cells(10 + i, 16)).Interior.
ColorIndex = 5
End If
Next i
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200802/1

run sub routine with multiple conditions
 

The table is usually 50 to 100 rows long. The starting row is A9 to P9.
The table is updated every day by adding the current stock data and
some calculated values. The table therefore is growing longer each
day by one row.
And yes, you are correct, I only would like to evaluate one row at a
time, and if all selected conditions are true, have that row colored blue.
The program then loops to the next row and repeats the testing with
those same conditions etc.
If you need more information George please let me know. Thanks
Hans

--
Message posted via http://www.officekb.com

run sub routine with multiple conditions
 

See how this does:
- Row loop added
- Logic reworked a bit
- Case statements changed to utilize row counter
Keep in mind that this is all untested air-code

Private Sub CommandButton1_Click()
Dim ctl As Control
Dim bolCheck As Boolean
Dim i As Integer 'Checkbox counter
Dim r as Long 'Row counter
Dim wks As Worksheet

Set wks = Worksheets("day1")
r = 9

' Stop if Col A of next row is empty (nothing to compare to, so last row can
never be Blue)
Do Until Len(Trim(wks.Cells(r + 1,1))) = 0
bolCheck = True
For i = 1 To 23
Set ctl = UserForm1.Controls("Checkbox" & i)
If ctl = True Then
'This checkbox is selected. Is its related condition True?
Select Case i
Case 1
'Is Col C of Next row greater than Col C of Current row?
If Not wks.Cells(r + 1 , 3).Value wks.Cells(r,3).Value Then
bolCheck = False 'spread
Case 2
'Is Col C of Next row less than than Col C of Current row?
If Not wks.Cells(r + 1, 3).Value < wks.Cells(r,3).Value Then
bolCheck = False 'vol
Case 3
'Is Col D of Next row greater than Col D of Current row?
If Not wks.Cells(r + 1, 4).Value wks.Cells(r, 4).Value Then
bolCheck = False
Case 4
'Is Col D of Next row less than Col D of Current row?
If Not wks.Cells(r + 1, 4).Value < wks.Cells(r, 4).Value Then
bolCheck = False
'...... other cases
Case 23
'Is Col N of Next row greater than Col N of Current row?
If Not wks.Cells(r + 1, 14).Value wks.Cells(r, 14).Value
Then bolCheck = False
End Select
End If
If bolCheck = False Then
'(Optional)
'A related condition is False. No need to continue checking any more
'boxes or conditions.
Exit For
End If
Next i

If BolCheck = True Then
' All selected conditions are true for this row (*or no conditions
(checkboxes) were selected*)
wks.Range(Cells(r, 2), wks.Cells(r, 16)).Interior.ColorIndex = 5
End If
r = r + 1
Loop

--
HTH,
George



"snah via OfficeKB.com" <u39929@uwe wrote in message
news:8016e583e74e0@uwe...
The table is usually 50 to 100 rows long. The starting row is A9 to P9.
The table is updated every day by adding the current stock data and
some calculated values. The table therefore is growing longer each
day by one row.
And yes, you are correct, I only would like to evaluate one row at a
time, and if all selected conditions are true, have that row colored blue.
The program then loops to the next row and repeats the testing with
those same conditions etc.
If you need more information George please let me know. Thanks
Hans

--
Message posted via http://www.officekb.com

run sub routine with multiple conditions
 

George, it works. Last night I finished the adaptation of your code, and it
works like a charm. The use of the Boolean variable was the answer I
was searching for so long. I added another routine that would in addition
to the rows, also color chart data points of a hi lo stock chart blue.
So now I can see right away whether these combination of those varies
conditions have a bullish or bearish potential for prediction.
Thanks so much for your time and code.
Hans

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200802/1