is there something wrong with the following line(assuming with Sheets
("Sheet1") and that i want the cell to be red?:
..Cells(rowCtr, colSig).Interior.ColorIndex = 3

i have used this method quite a bit and it works in other scripts that i
use on different reports.

at the end of execution the cells are not colored, however using
msgbox(.cells(y, x).interior.colorindex) gives me 3, so i know it is
executing.

is there some setting in the sheet itself that would keep this from
executing with the desired results(i want the cell to be filled red).

the only think i could think of to check is the pattern property of
interior and it is 1 (solid) so that seems correct to me.

anyone have any ideas?

TIA
bst

if you already have conditional formatting applied to that cell, then
it will take precedence over the colorindex command......
try clearing any conditional formatting & running it again.
hth
:)
susan


On Jun 27, 3:53*pm, bst wrote:
is there something wrong with the following line(assuming with Sheets
("Sheet1") and that i want the cell to be red?:
.Cells(rowCtr, colSig).Interior.ColorIndex = 3

i have used this method quite a bit and it works in other scripts that i
use on different reports.

at the end of execution the cells are not colored, however using
msgbox(.cells(y, x).interior.colorindex) gives me 3, so i know it is
executing.

is there some setting in the sheet itself that would keep this from
executing with the desired results(i want the cell to be filled red).

the only think i could think of to check is the pattern property of
interior and it is 1 (solid) so that seems correct to me.

anyone have any ideas?

TIA
bst

Is there a conditional format on the cell. Conditional formatting overrides
the interior colour of a cell.
--
HTH...

Jim Thomlinson


"bst" wrote:

is there something wrong with the following line(assuming with Sheets
("Sheet1") and that i want the cell to be red?:
..Cells(rowCtr, colSig).Interior.ColorIndex = 3

i have used this method quite a bit and it works in other scripts that i
use on different reports.

at the end of execution the cells are not colored, however using
msgbox(.cells(y, x).interior.colorindex) gives me 3, so i know it is
executing.

is there some setting in the sheet itself that would keep this from
executing with the desired results(i want the cell to be filled red).

the only think i could think of to check is the pattern property of
interior and it is 1 (solid) so that seems correct to me.

anyone have any ideas?

TIA
bst

point me in the direction to learn more about conditional formatting
please. probably since i don't know anything about it, its not there,
but i don't build the raw report so maybe it is there.

thanks for the quick reply

bst

Susan wrote in
:

if you already have conditional formatting applied to that cell, then
it will take precedence over the colorindex command......
try clearing any conditional formatting & running it again.
hth
:)
susan


On Jun 27, 3:53*pm, bst wrote:
is there something wrong with the following line(assuming with Sheets
("Sheet1") and that i want the cell to be red?:
.Cells(rowCtr, colSig).Interior.ColorIndex = 3

i have used this method quite a bit and it works in other scripts
that i use on different reports.

at the end of execution the cells are not colored, however using
msgbox(.cells(y, x).interior.colorindex) gives me 3, so i know it is
executing.

is there some setting in the sheet itself that would keep this from
executing with the desired results(i want the cell to be filled red).

the only think i could think of to check is the pattern property of
interior and it is 1 (solid) so that seems correct to me.

anyone have any ideas?

TIA
bst

in the worksheet, on the main toolbar, check Format, Conditional
Formatting. use the Delete button to delete whatever is there.
hope it helps
susan


On Jun 27, 4:09*pm, bst wrote:
point me in the direction to learn more about conditional formatting
please. *probably since i don't know anything about it, its not there,
but i don't build the raw report so maybe it is there.

thanks for the quick reply

bst

Susan wrote :



if you already have conditional formatting applied to that cell, then
it will take precedence over the colorindex command......
try clearing any conditional formatting & running it again.
hth
:)
susan

On Jun 27, 3:53*pm, bst wrote:
is there something wrong with the following line(assuming with Sheets
("Sheet1") and that i want the cell to be red?:
.Cells(rowCtr, colSig).Interior.ColorIndex = 3

i have used this method quite a bit and it works in other scripts
that i use on different reports.

at the end of execution the cells are not colored, however using
msgbox(.cells(y, x).interior.colorindex) gives me 3, so i know it is
executing.

is there some setting in the sheet itself that would keep this from
executing with the desired results(i want the cell to be filled red).

the only think i could think of to check is the pattern property of
interior and it is 1 (solid) so that seems correct to me.

anyone have any ideas?

TIA
bst- Hide quoted text -

- Show quoted text -

ok, i have learned about conditional formating. maybe for what i want to
do i should try using it instead. i am pretty sure however that there is
no conditional formating on the cell already. this actually looks like a
pretty neat method. is it any better or quicker than?: if conditon
set cell color
end if

still, i don't think that is why my original code will not work. any
other ideas. i'm working with excel 2000 if that matters. um also screen
update is turned off, but then turned back on at the end of the sheet. i
dont think this matters because i do the same in other macros as well
with desired results.

thanks
bst

bst wrote in
:

point me in the direction to learn more about conditional formatting
please. probably since i don't know anything about it, its not there,
but i don't build the raw report so maybe it is there.

thanks for the quick reply

bst

Susan wrote in
:

if you already have conditional formatting applied to that cell, then
it will take precedence over the colorindex command......
try clearing any conditional formatting & running it again.
hth
:)
susan


On Jun 27, 3:53*pm, bst wrote:
is there something wrong with the following line(assuming with
Sheets ("Sheet1") and that i want the cell to be red?:
.Cells(rowCtr, colSig).Interior.ColorIndex = 3

i have used this method quite a bit and it works in other scripts
that i use on different reports.

at the end of execution the cells are not colored, however using
msgbox(.cells(y, x).interior.colorindex) gives me 3, so i know it is
executing.

is there some setting in the sheet itself that would keep this from
executing with the desired results(i want the cell to be filled
red).

the only think i could think of to check is the pattern property of
interior and it is 1 (solid) so that seems correct to me.

anyone have any ideas?

TIA
bst

the cell has not conditional formating

bst wrote in
:

ok, i have learned about conditional formating. maybe for what i want
to do i should try using it instead. i am pretty sure however that
there is no conditional formating on the cell already. this actually
looks like a pretty neat method. is it any better or quicker than?: if
conditon
set cell color
end if

still, i don't think that is why my original code will not work. any
other ideas. i'm working with excel 2000 if that matters. um also
screen update is turned off, but then turned back on at the end of the
sheet. i dont think this matters because i do the same in other macros
as well with desired results.

thanks
bst

bst wrote in
:

point me in the direction to learn more about conditional formatting
please. probably since i don't know anything about it, its not
there, but i don't build the raw report so maybe it is there.

thanks for the quick reply

bst

Susan wrote in
news:0ebf7d9c-a77f-4f13-aa79-0b8a1ebf8a50@
34g2000hsh.googlegroups.com:

if you already have conditional formatting applied to that cell,
then it will take precedence over the colorindex command......
try clearing any conditional formatting & running it again.
hth
:)
susan


On Jun 27, 3:53*pm, bst wrote:
is there something wrong with the following line(assuming with
Sheets ("Sheet1") and that i want the cell to be red?:
.Cells(rowCtr, colSig).Interior.ColorIndex = 3

i have used this method quite a bit and it works in other scripts
that i use on different reports.

at the end of execution the cells are not colored, however using
msgbox(.cells(y, x).interior.colorindex) gives me 3, so i know it
is executing.

is there some setting in the sheet itself that would keep this from
executing with the desired results(i want the cell to be filled
red).

the only think i could think of to check is the pattern property of
interior and it is 1 (solid) so that seems correct to me.

anyone have any ideas?

TIA
bst

To identify cells with conditional formatting, click
EditGoToSpecialConditional FormatsAll. They might not all be for
Interior.ColorIndex but you can tell which ones you need to check.

"bst" wrote:

point me in the direction to learn more about conditional formatting
please. probably since i don't know anything about it, its not there,
but i don't build the raw report so maybe it is there.

thanks for the quick reply

bst

Susan wrote in
:

if you already have conditional formatting applied to that cell, then
it will take precedence over the colorindex command......
try clearing any conditional formatting & running it again.
hth
:)
susan


On Jun 27, 3:53 pm, bst wrote:
is there something wrong with the following line(assuming with Sheets
("Sheet1") and that i want the cell to be red?:
.Cells(rowCtr, colSig).Interior.ColorIndex = 3

i have used this method quite a bit and it works in other scripts
that i use on different reports.

at the end of execution the cells are not colored, however using
msgbox(.cells(y, x).interior.colorindex) gives me 3, so i know it is
executing.

is there some setting in the sheet itself that would keep this from
executing with the desired results(i want the cell to be filled red).

the only think i could think of to check is the pattern property of
interior and it is 1 (solid) so that seems correct to me.

anyone have any ideas?

TIA
bst

none of the cells have any conditional formating. i've decided to go
ahead an copy the code to the program here for anyone to examine, maybe
i've done something wrong before that point that i have not caught. also,
if i remember correctly the way i copy the row to another sheet, the
color does not go with it, that is fine for now, as i'll work that after
i get the color to work in the first place. it is kind of a long macro,
but i believe it is documented well enough for anyone to understand what
is happening. since it is frowned upon to attach an excel sheet i'll give
you an idea of what the sheet looks like before i paste code. the logic
for the code works. i have stepped through the program and i see the
color cell lines execute, just no result is visible at or during execution
(with screen updating turned on).

the columns are a through u (incase the formatting of this message does
not come out well on your reader). the first row of the sheet is a header
file. currently the sheet has 12120 rows.
the first column is any combintion of nc[a,c,g,i,j,k,l,r,w][0-9][0-9]
[0-9] such as nca300, or nck511, etc.the second and third column are
irrevelant, but needed for spacing if you dont want to modify the code.
the 4th column is a time (as text). the forth column is any string.
columns 6-12 are irrevelent, but again needed for spacing. 13-16 should
be the word "TRUE' or "FALSE". columns 20-21 are irrevelant. there is no
cell formatting and you should assume everything is formatted as general.
an example row would like the following:
nca100 ncalddd405 100 05:00 BOA NYYYYNN jonestown 4mainst jersey nj 28854
pu TRUE FALSE TRUE TRUE FALSE FALSE MIKE GHH123 IER2.

the code follows:
Sub stoplevelscanningsort()
'make program run faster
Application.ScreenUpdating = False
Sheets.Add.Name = "NCC"
Sheets.Add.Name = "NCA"
Sheets.Add.Name = "NCW"
Sheets.Add.Name = "NCK"
Sheets.Add.Name = "NCR"
Sheets.Add.Name = "NCG"
Sheets.Add.Name = "NCL"
Sheets.Add.Name = "NCI"

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''
'declare necessary things
Dim terminalNames(1 To 9) As String
terminalNames(1) = "NCC"
terminalNames(2) = "NCA"
terminalNames(3) = "NCW"
terminalNames(4) = "NCK"
terminalNames(5) = "NCR"
terminalNames(6) = "NCG"
terminalNames(7) = "NCL"
terminalNames(8) = "NCI"
terminalNames(9) = "NCJ"

Dim ncg100, nca102 As String
nca102 = "NCA102"
ncg100 = "NCG100"

Dim xCtr As Integer, totalRows As Long, client As String, route As
String, time As Date, _
rowCtr As Long, strongBox As String, sig As String

Dim terminalRowCtr(1 To 8) As Long
For xCtr = 1 To 8
terminalRowCtr(xCtr) = 1
Next xCtr

Const colSB As Integer = 16
Const colRoute As Integer = 1
Const colClient As Integer = 5
Const colStopScan As Integer = 14
Const colRouteScan As Integer = 13
Const colSig As Integer = 15
Const colTime As Integer = 4
Sheets("Sheet1").Activate
totalRows = ActiveSheet.UsedRange.Rows.Count
'end declarations

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''
'go through the sheet and mark cells that need to be reviewed.
'if strong box is true and the route is either GSO, CLT, or RDU and
the client is
'WACHOVIA or BOA or SUNTRUST then it is fine. if strong box is false
and the route
'is either GSO, CLT, or RDU and the client is WACHOVIA or BOA or
SUNTRUST then color
'cell red (the stop should be using a strong box) if signature is
true and scheduled
'time 1700 and scheduled time is < 500 then color cell red (this is
afterhours, should
'a sig be required?). if sig is false and scheduled time is 500 and
scheduled time is
'< 1700 then color cell red (this is during normal hours, a signature
should be captured).
'if stop scan is false then mark as red (we should scan every stop if
at the very least to
'get a electronic time stamp)if routescan is false then mark as red
(every route should
'be scanning right?)move each line to a separate sheet for each
terminal.

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''
With Sheets("Sheet1")
For rowCtr = 2 To totalRows
route = UCase(.Cells(rowCtr, colRoute).Value)
client = UCase(.Cells(rowCtr, colClient).Value)
time = TimeValue(.Cells(rowCtr, colTime).Value)
strongBox = UCase(.Cells(rowCtr, colSB).Value)
sig = UCase(.Cells(rowCtr, colSig).Value)

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
'check strongbox
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''
If (strongBox = "FALSE" And sbClientBool(client) And sbRouteBool
(route)) Then
.Cells(rowCtr, colSB).Interior.ColorIndex = 3 'stop should
use strongbox
End If
If (strongBox = "TRUE" And Not sbRouteBool(route) And Not
sbClientBool(client)) Then
.Cells(rowCtr, colSB).Interior.ColorIndex = 3 'stop should
not use strongobox
End If

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''
'check stopscan

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''
If UCase(.Cells(rowCtr, colStopScan).Value) = "FALSE" Then
.Cells(rowCtr, colStopScan).Interior.ColorIndex = 3 'all
stops should be scan enabled
End If

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''
'check route scan

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
If UCase(.Cells(rowCtr, colRouteScan).Value) = "FALSE" Then
.Cells(rowCtr, colRouteScan).Interior.ColorIndex = 3 'all
routes should be scan enabled
End If

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''
'check for signature enabled stop

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''
If (sig = "TRUE" And shouldNotSigBool(time)) Then
.Cells(rowCtr, colSig).Interior.ColorIndex = 3 'after hours,
no sig needed
End If
If (sig = "FALSE" And shouldSigBool(time)) Then
.Cells(rowCtr, colSig).Interior.ColorIndex = 3 'normal hours,
sig needed
End If

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''
'copy row to appropiate sheet

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''
For xCtr = 1 To 9
If InStr(route, terminalNames(xCtr)) 0 Then
If xCtr = 9 Then 'ncj = nci
Sheets(terminalNames(8)).Rows(terminalRowCtr
(8)).Value = .Rows(rowCtr).Value
terminalRowCtr(8) = terminalRowCtr(8) + 1
Exit For
End If
If route = ncg100 Then 'ncg100 is a rdu route
Sheets(terminalNames(5)).Rows(terminalRowCtr
(5)).Value = .Rows(rowCtr).Value
terminalRowCtr(5) = terminalRowCtr(5) + 1
Exit For
End If
If route = nca102 Then 'nca102 is a wlk route
Sheets(terminalNames(3)).Rows(terminalRowCtr
(3)).Value = .Rows(rowCtr).Value
terminalRowCtr(3) = terminalRowCtr(3) + 1
Exit For
End If
Sheets(terminalNames(xCtr)).Rows(terminalRowCtr
(xCtr)).Value = .Rows(rowCtr).Value
terminalRowCtr(xCtr) = terminalRowCtr(xCtr) + 1
Exit For
End If
Next xCtr
Next rowCtr
End With


'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''
'copy header row to each sheet

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''
Sheets("Sheet1").Activate
For xCtr = 1 To 8
Rows(1).Select
Selection.Copy
Sheets(terminalNames(xCtr)).Select
Selection.Insert shift:=xlDown
Sheets("Sheet1").Activate
Next xCtr
Application.ScreenUpdating = True
End Sub

Function sbClientBool(client As String) As Boolean
If UCase(client) = "BOA" Then
sbClientBool = True
Exit Function
End If
If UCase(client) = "WACHOVIA" Then
sbClientBool = True
Exit Function
End If
If UCase(client) = "SUNTRUST" Then
sbClientBool = True
Exit Function
End If
sbClientBool = False
End Function

Function sbRouteBool(route As String) As Boolean
If InStr(UCase(route), "NCR") 0 Then
sbRouteBool = True
Exit Function
End If
If InStr(UCase(route), "NCC") 0 Then
sbRouteBool = True
Exit Function
End If
If InStr(UCase(route), "NCK") 0 Then
sbRouteBool = True
Exit Function
End If
sbRouteBool = False
End Function

Function shouldSigBool(time As Date) As Boolean
Const am As Date = #5:00:00 AM#
Const pm As Date = #5:00:00 PM#
If (time am And time < pm) Then
shouldSigBool = True
Else
shouldSigBool = False
End If
End Function

Function shouldNotSigBool(time As Date) As Boolean
Const am As Date = #5:00:00 AM#
Const pm As Date = #5:00:00 PM#
If (time < am And time pm) Then
shouldNotSigBool = True
Else
shouldNotSigBool = False
End If
End Function

tia
bst


On Fri, 27 Jun 2008 13:25:00 -0700, JLGWhiz wrote:

To identify cells with conditional formatting, click
EditGoToSpecialConditional FormatsAll. They might not all be for
Interior.ColorIndex but you can tell which ones you need to check.

Just a side note, don't know if it would affect the results, but you could
delare colSB as a variable:

Dim colSB As Long
colSb = 16

instead of a constant.

"bst" wrote:

none of the cells have any conditional formating. i've decided to go
ahead an copy the code to the program here for anyone to examine, maybe
i've done something wrong before that point that i have not caught. also,
if i remember correctly the way i copy the row to another sheet, the
color does not go with it, that is fine for now, as i'll work that after
i get the color to work in the first place. it is kind of a long macro,
but i believe it is documented well enough for anyone to understand what
is happening. since it is frowned upon to attach an excel sheet i'll give
you an idea of what the sheet looks like before i paste code. the logic
for the code works. i have stepped through the program and i see the
color cell lines execute, just no result is visible at or during execution
(with screen updating turned on).

the columns are a through u (incase the formatting of this message does
not come out well on your reader). the first row of the sheet is a header
file. currently the sheet has 12120 rows.
the first column is any combintion of nc[a,c,g,i,j,k,l,r,w][0-9][0-9]
[0-9] such as nca300, or nck511, etc.the second and third column are
irrevelant, but needed for spacing if you dont want to modify the code.
the 4th column is a time (as text). the forth column is any string.
columns 6-12 are irrevelent, but again needed for spacing. 13-16 should
be the word "TRUE' or "FALSE". columns 20-21 are irrevelant. there is no
cell formatting and you should assume everything is formatted as general.
an example row would like the following:
nca100 ncalddd405 100 05:00 BOA NYYYYNN jonestown 4mainst jersey nj 28854
pu TRUE FALSE TRUE TRUE FALSE FALSE MIKE GHH123 IER2.

the code follows:
Sub stoplevelscanningsort()
'make program run faster
Application.ScreenUpdating = False
Sheets.Add.Name = "NCC"
Sheets.Add.Name = "NCA"
Sheets.Add.Name = "NCW"
Sheets.Add.Name = "NCK"
Sheets.Add.Name = "NCR"
Sheets.Add.Name = "NCG"
Sheets.Add.Name = "NCL"
Sheets.Add.Name = "NCI"

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''
'declare necessary things
Dim terminalNames(1 To 9) As String
terminalNames(1) = "NCC"
terminalNames(2) = "NCA"
terminalNames(3) = "NCW"
terminalNames(4) = "NCK"
terminalNames(5) = "NCR"
terminalNames(6) = "NCG"
terminalNames(7) = "NCL"
terminalNames(8) = "NCI"
terminalNames(9) = "NCJ"

Dim ncg100, nca102 As String
nca102 = "NCA102"
ncg100 = "NCG100"

Dim xCtr As Integer, totalRows As Long, client As String, route As
String, time As Date, _
rowCtr As Long, strongBox As String, sig As String

Dim terminalRowCtr(1 To 8) As Long
For xCtr = 1 To 8
terminalRowCtr(xCtr) = 1
Next xCtr

Const colSB As Integer = 16
Const colRoute As Integer = 1
Const colClient As Integer = 5
Const colStopScan As Integer = 14
Const colRouteScan As Integer = 13
Const colSig As Integer = 15
Const colTime As Integer = 4
Sheets("Sheet1").Activate
totalRows = ActiveSheet.UsedRange.Rows.Count
'end declarations

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''
'go through the sheet and mark cells that need to be reviewed.
'if strong box is true and the route is either GSO, CLT, or RDU and
the client is
'WACHOVIA or BOA or SUNTRUST then it is fine. if strong box is false
and the route
'is either GSO, CLT, or RDU and the client is WACHOVIA or BOA or
SUNTRUST then color
'cell red (the stop should be using a strong box) if signature is
true and scheduled
'time 1700 and scheduled time is < 500 then color cell red (this is
afterhours, should
'a sig be required?). if sig is false and scheduled time is 500 and
scheduled time is
'< 1700 then color cell red (this is during normal hours, a signature
should be captured).
'if stop scan is false then mark as red (we should scan every stop if
at the very least to
'get a electronic time stamp)if routescan is false then mark as red
(every route should
'be scanning right?)move each line to a separate sheet for each
terminal.

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''
With Sheets("Sheet1")
For rowCtr = 2 To totalRows
route = UCase(.Cells(rowCtr, colRoute).Value)
client = UCase(.Cells(rowCtr, colClient).Value)
time = TimeValue(.Cells(rowCtr, colTime).Value)
strongBox = UCase(.Cells(rowCtr, colSB).Value)
sig = UCase(.Cells(rowCtr, colSig).Value)

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
'check strongbox
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''
If (strongBox = "FALSE" And sbClientBool(client) And sbRouteBool
(route)) Then
.Cells(rowCtr, colSB).Interior.ColorIndex = 3 'stop should
use strongbox
End If
If (strongBox = "TRUE" And Not sbRouteBool(route) And Not
sbClientBool(client)) Then
.Cells(rowCtr, colSB).Interior.ColorIndex = 3 'stop should
not use strongobox
End If

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''
'check stopscan

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''
If UCase(.Cells(rowCtr, colStopScan).Value) = "FALSE" Then
.Cells(rowCtr, colStopScan).Interior.ColorIndex = 3 'all
stops should be scan enabled
End If

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''
'check route scan

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
If UCase(.Cells(rowCtr, colRouteScan).Value) = "FALSE" Then
.Cells(rowCtr, colRouteScan).Interior.ColorIndex = 3 'all
routes should be scan enabled
End If

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''
'check for signature enabled stop

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''
If (sig = "TRUE" And shouldNotSigBool(time)) Then
.Cells(rowCtr, colSig).Interior.ColorIndex = 3 'after hours,
no sig needed
End If
If (sig = "FALSE" And shouldSigBool(time)) Then
.Cells(rowCtr, colSig).Interior.ColorIndex = 3 'normal hours,
sig needed
End If

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''
'copy row to appropiate sheet

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''
For xCtr = 1 To 9
If InStr(route, terminalNames(xCtr)) 0 Then
If xCtr = 9 Then 'ncj = nci
Sheets(terminalNames(8)).Rows(terminalRowCtr
(8)).Value = .Rows(rowCtr).Value
terminalRowCtr(8) = terminalRowCtr(8) + 1
Exit For
End If
If route = ncg100 Then 'ncg100 is a rdu route
Sheets(terminalNames(5)).Rows(terminalRowCtr
(5)).Value = .Rows(rowCtr).Value
terminalRowCtr(5) = terminalRowCtr(5) + 1
Exit For
End If
If route = nca102 Then 'nca102 is a wlk route
Sheets(terminalNames(3)).Rows(terminalRowCtr
(3)).Value = .Rows(rowCtr).Value
terminalRowCtr(3) = terminalRowCtr(3) + 1
Exit For
End If
Sheets(terminalNames(xCtr)).Rows(terminalRowCtr
(xCtr)).Value = .Rows(rowCtr).Value
terminalRowCtr(xCtr) = terminalRowCtr(xCtr) + 1
Exit For
End If
Next xCtr
Next rowCtr
End With


'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''
'copy header row to each sheet

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''
Sheets("Sheet1").Activate
For xCtr = 1 To 8
Rows(1).Select
Selection.Copy
Sheets(terminalNames(xCtr)).Select
Selection.Insert shift:=xlDown
Sheets("Sheet1").Activate
Next xCtr
Application.ScreenUpdating = True
End Sub

Function sbClientBool(client As String) As Boolean
If UCase(client) = "BOA" Then
sbClientBool = True
Exit Function
End If
If UCase(client) = "WACHOVIA" Then
sbClientBool = True
Exit Function
End If
If UCase(client) = "SUNTRUST" Then
sbClientBool = True
Exit Function
End If
sbClientBool = False
End Function

Function sbRouteBool(route As String) As Boolean
If InStr(UCase(route), "NCR") 0 Then
sbRouteBool = True
Exit Function
End If
If InStr(UCase(route), "NCC") 0 Then
sbRouteBool = True
Exit Function
End If
If InStr(UCase(route), "NCK") 0 Then
sbRouteBool = True
Exit Function
End If
sbRouteBool = False
End Function

Function shouldSigBool(time As Date) As Boolean
Const am As Date = #5:00:00 AM#
Const pm As Date = #5:00:00 PM#
If (time am And time < pm) Then
shouldSigBool = True
Else
shouldSigBool = False
End If
End Function

Function shouldNotSigBool(time As Date) As Boolean
Const am As Date = #5:00:00 AM#
Const pm As Date = #5:00:00 PM#
If (time < am And time pm) Then
shouldNotSigBool = True
Else
shouldNotSigBool = False
End If
End Function

tia
bst


On Fri, 27 Jun 2008 13:25:00 -0700, JLGWhiz wrote:

To identify cells with conditional formatting, click
EditGoToSpecialConditional FormatsAll. They might not all be for
Interior.ColorIndex but you can tell which ones you need to check.

i have severel other things declared as constants. what made you pick out
colSB as an issue? how would making it variable effect the program
results. also why do you think it should be long as opposed to integer?
On Fri, 27 Jun 2008 19:13:01 -0700, JLGWhiz wrote:

Just a side note, don't know if it would affect the results, but you
could delare colSB as a variable:

Dim colSB As Long
colSb = 16

instead of a constant.

"bst" wrote:

none of the cells have any conditional formating. i've decided to go
ahead an copy the code to the program here for anyone to examine, maybe
i've done something wrong before that point that i have not caught.
also, if i remember correctly the way i copy the row to another sheet,
the color does not go with it, that is fine for now, as i'll work that
after i get the color to work in the first place. it is kind of a long
macro, but i believe it is documented well enough for anyone to
understand what is happening. since it is frowned upon to attach an
excel sheet i'll give you an idea of what the sheet looks like before i
paste code. the logic for the code works. i have stepped through the
program and i see the color cell lines execute, just no result is
visible at or during execution (with screen updating turned on).

the columns are a through u (incase the formatting of this message does
not come out well on your reader). the first row of the sheet is a
header file. currently the sheet has 12120 rows. the first column is
any combintion of nc[a,c,g,i,j,k,l,r,w][0-9][0-9] [0-9] such as nca300,
or nck511, etc.the second and third column are irrevelant, but needed
for spacing if you dont want to modify the code. the 4th column is a
time (as text). the forth column is any string. columns 6-12 are
irrevelent, but again needed for spacing. 13-16 should be the word
"TRUE' or "FALSE". columns 20-21 are irrevelant. there is no cell
formatting and you should assume everything is formatted as general. an
example row would like the following: nca100 ncalddd405 100 05:00 BOA
NYYYYNN jonestown 4mainst jersey nj 28854 pu TRUE FALSE TRUE TRUE FALSE
FALSE MIKE GHH123 IER2.

the code follows:
Sub stoplevelscanningsort()
'make program run faster
Application.ScreenUpdating = False
Sheets.Add.Name = "NCC"
Sheets.Add.Name = "NCA"
Sheets.Add.Name = "NCW"
Sheets.Add.Name = "NCK"
Sheets.Add.Name = "NCR"
Sheets.Add.Name = "NCG"
Sheets.Add.Name = "NCL"
Sheets.Add.Name = "NCI"

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''
'declare necessary things
Dim terminalNames(1 To 9) As String
terminalNames(1) = "NCC"
terminalNames(2) = "NCA"
terminalNames(3) = "NCW"
terminalNames(4) = "NCK"
terminalNames(5) = "NCR"
terminalNames(6) = "NCG"
terminalNames(7) = "NCL"
terminalNames(8) = "NCI"
terminalNames(9) = "NCJ"

Dim ncg100, nca102 As String
nca102 = "NCA102"
ncg100 = "NCG100"

Dim xCtr As Integer, totalRows As Long, client As String, route As
String, time As Date, _
rowCtr As Long, strongBox As String, sig As String

Dim terminalRowCtr(1 To 8) As Long
For xCtr = 1 To 8
terminalRowCtr(xCtr) = 1
Next xCtr

Const colSB As Integer = 16
Const colRoute As Integer = 1
Const colClient As Integer = 5
Const colStopScan As Integer = 14
Const colRouteScan As Integer = 13
Const colSig As Integer = 15
Const colTime As Integer = 4
Sheets("Sheet1").Activate
totalRows = ActiveSheet.UsedRange.Rows.Count 'end declarations


'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''
'go through the sheet and mark cells that need to be reviewed. 'if
strong box is true and the route is either GSO, CLT, or RDU and
the client is
'WACHOVIA or BOA or SUNTRUST then it is fine. if strong box is
false
and the route
'is either GSO, CLT, or RDU and the client is WACHOVIA or BOA or
SUNTRUST then color
'cell red (the stop should be using a strong box) if signature is
true and scheduled
'time 1700 and scheduled time is < 500 then color cell red (this
is
afterhours, should
'a sig be required?). if sig is false and scheduled time is 500
and
scheduled time is
'< 1700 then color cell red (this is during normal hours, a
signature
should be captured).
'if stop scan is false then mark as red (we should scan every stop
if
at the very least to
'get a electronic time stamp)if routescan is false then mark as red
(every route should
'be scanning right?)move each line to a separate sheet for each
terminal.

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''
With Sheets("Sheet1")
For rowCtr = 2 To totalRows
route = UCase(.Cells(rowCtr, colRoute).Value) client =
UCase(.Cells(rowCtr, colClient).Value) time =
TimeValue(.Cells(rowCtr, colTime).Value) strongBox =
UCase(.Cells(rowCtr, colSB).Value) sig = UCase(.Cells(rowCtr,
colSig).Value)

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
'check strongbox

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''
If (strongBox = "FALSE" And sbClientBool(client) And
sbRouteBool
(route)) Then
.Cells(rowCtr, colSB).Interior.ColorIndex = 3 'stop should
use strongbox
End If
If (strongBox = "TRUE" And Not sbRouteBool(route) And Not
sbClientBool(client)) Then
.Cells(rowCtr, colSB).Interior.ColorIndex = 3 'stop should
not use strongobox
End If

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''
'check stopscan

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''
If UCase(.Cells(rowCtr, colStopScan).Value) = "FALSE" Then
.Cells(rowCtr, colStopScan).Interior.ColorIndex = 3 'all
stops should be scan enabled
End If

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''
'check route scan

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
If UCase(.Cells(rowCtr, colRouteScan).Value) = "FALSE" Then
.Cells(rowCtr, colRouteScan).Interior.ColorIndex = 3 'all
routes should be scan enabled
End If

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''
'check for signature enabled stop

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''
If (sig = "TRUE" And shouldNotSigBool(time)) Then
.Cells(rowCtr, colSig).Interior.ColorIndex = 3 'after
hours,
no sig needed
End If
If (sig = "FALSE" And shouldSigBool(time)) Then
.Cells(rowCtr, colSig).Interior.ColorIndex = 3 'normal
hours,
sig needed
End If

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''
'copy row to appropiate sheet

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''
For xCtr = 1 To 9
If InStr(route, terminalNames(xCtr)) 0 Then
If xCtr = 9 Then 'ncj = nci
Sheets(terminalNames(8)).Rows(terminalRowCtr
(8)).Value = .Rows(rowCtr).Value
terminalRowCtr(8) = terminalRowCtr(8) + 1 Exit For
End If
If route = ncg100 Then 'ncg100 is a rdu route
Sheets(terminalNames(5)).Rows(terminalRowCtr
(5)).Value = .Rows(rowCtr).Value
terminalRowCtr(5) = terminalRowCtr(5) + 1 Exit For
End If
If route = nca102 Then 'nca102 is a wlk route
Sheets(terminalNames(3)).Rows(terminalRowCtr
(3)).Value = .Rows(rowCtr).Value
terminalRowCtr(3) = terminalRowCtr(3) + 1 Exit For
End If
Sheets(terminalNames(xCtr)).Rows(terminalRowCtr
(xCtr)).Value = .Rows(rowCtr).Value
terminalRowCtr(xCtr) = terminalRowCtr(xCtr) + 1 Exit
For
End If
Next xCtr
Next rowCtr
End With



'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''
'copy header row to each sheet

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''
Sheets("Sheet1").Activate
For xCtr = 1 To 8
Rows(1).Select
Selection.Copy
Sheets(terminalNames(xCtr)).Select
Selection.Insert shift:=xlDown
Sheets("Sheet1").Activate
Next xCtr
Application.ScreenUpdating = True
End Sub

Function sbClientBool(client As String) As Boolean
If UCase(client) = "BOA" Then
sbClientBool = True
Exit Function
End If
If UCase(client) = "WACHOVIA" Then
sbClientBool = True
Exit Function
End If
If UCase(client) = "SUNTRUST" Then
sbClientBool = True
Exit Function
End If
sbClientBool = False
End Function

Function sbRouteBool(route As String) As Boolean
If InStr(UCase(route), "NCR") 0 Then
sbRouteBool = True
Exit Function
End If
If InStr(UCase(route), "NCC") 0 Then
sbRouteBool = True
Exit Function
End If
If InStr(UCase(route), "NCK") 0 Then
sbRouteBool = True
Exit Function
End If
sbRouteBool = False
End Function

Function shouldSigBool(time As Date) As Boolean
Const am As Date = #5:00:00 AM#
Const pm As Date = #5:00:00 PM#
If (time am And time < pm) Then
shouldSigBool = True
Else
shouldSigBool = False
End If
End Function

Function shouldNotSigBool(time As Date) As Boolean
Const am As Date = #5:00:00 AM#
Const pm As Date = #5:00:00 PM#
If (time < am And time pm) Then
shouldNotSigBool = True
Else
shouldNotSigBool = False
End If
End Function

tia
bst


On Fri, 27 Jun 2008 13:25:00 -0700, JLGWhiz wrote:

To identify cells with conditional formatting, click
EditGoToSpecialConditional FormatsAll. They might not all be for
Interior.ColorIndex but you can tell which ones you need to check.

what made you pick out colSB as an issue?

That was used in the cell reference and when I checked to see if it had been
given a value I noticed the declaration as a constant integer. The other
column references could also be declared as Long which takes less bytes than
Integer. Like I said, it probably would not affect execution, but was just a
side note.



"bst" wrote:

i have severel other things declared as constants. what made you pick out
colSB as an issue? how would making it variable effect the program
results. also why do you think it should be long as opposed to integer?
On Fri, 27 Jun 2008 19:13:01 -0700, JLGWhiz wrote:

Just a side note, don't know if it would affect the results, but you
could delare colSB as a variable:

Dim colSB As Long
colSb = 16

instead of a constant.

"bst" wrote:

none of the cells have any conditional formating. i've decided to go
ahead an copy the code to the program here for anyone to examine, maybe
i've done something wrong before that point that i have not caught.
also, if i remember correctly the way i copy the row to another sheet,
the color does not go with it, that is fine for now, as i'll work that
after i get the color to work in the first place. it is kind of a long
macro, but i believe it is documented well enough for anyone to
understand what is happening. since it is frowned upon to attach an
excel sheet i'll give you an idea of what the sheet looks like before i
paste code. the logic for the code works. i have stepped through the
program and i see the color cell lines execute, just no result is
visible at or during execution (with screen updating turned on).

the columns are a through u (incase the formatting of this message does
not come out well on your reader). the first row of the sheet is a
header file. currently the sheet has 12120 rows. the first column is
any combintion of nc[a,c,g,i,j,k,l,r,w][0-9][0-9] [0-9] such as nca300,
or nck511, etc.the second and third column are irrevelant, but needed
for spacing if you dont want to modify the code. the 4th column is a
time (as text). the forth column is any string. columns 6-12 are
irrevelent, but again needed for spacing. 13-16 should be the word
"TRUE' or "FALSE". columns 20-21 are irrevelant. there is no cell
formatting and you should assume everything is formatted as general. an
example row would like the following: nca100 ncalddd405 100 05:00 BOA
NYYYYNN jonestown 4mainst jersey nj 28854 pu TRUE FALSE TRUE TRUE FALSE
FALSE MIKE GHH123 IER2.

the code follows:
Sub stoplevelscanningsort()
'make program run faster
Application.ScreenUpdating = False
Sheets.Add.Name = "NCC"
Sheets.Add.Name = "NCA"
Sheets.Add.Name = "NCW"
Sheets.Add.Name = "NCK"
Sheets.Add.Name = "NCR"
Sheets.Add.Name = "NCG"
Sheets.Add.Name = "NCL"
Sheets.Add.Name = "NCI"

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''
'declare necessary things
Dim terminalNames(1 To 9) As String
terminalNames(1) = "NCC"
terminalNames(2) = "NCA"
terminalNames(3) = "NCW"
terminalNames(4) = "NCK"
terminalNames(5) = "NCR"
terminalNames(6) = "NCG"
terminalNames(7) = "NCL"
terminalNames(8) = "NCI"
terminalNames(9) = "NCJ"

Dim ncg100, nca102 As String
nca102 = "NCA102"
ncg100 = "NCG100"

Dim xCtr As Integer, totalRows As Long, client As String, route As
String, time As Date, _
rowCtr As Long, strongBox As String, sig As String

Dim terminalRowCtr(1 To 8) As Long
For xCtr = 1 To 8
terminalRowCtr(xCtr) = 1
Next xCtr

Const colSB As Integer = 16
Const colRoute As Integer = 1
Const colClient As Integer = 5
Const colStopScan As Integer = 14
Const colRouteScan As Integer = 13
Const colSig As Integer = 15
Const colTime As Integer = 4
Sheets("Sheet1").Activate
totalRows = ActiveSheet.UsedRange.Rows.Count 'end declarations


'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''
'go through the sheet and mark cells that need to be reviewed. 'if
strong box is true and the route is either GSO, CLT, or RDU and
the client is
'WACHOVIA or BOA or SUNTRUST then it is fine. if strong box is
false
and the route
'is either GSO, CLT, or RDU and the client is WACHOVIA or BOA or
SUNTRUST then color
'cell red (the stop should be using a strong box) if signature is
true and scheduled
'time 1700 and scheduled time is < 500 then color cell red (this
is
afterhours, should
'a sig be required?). if sig is false and scheduled time is 500
and
scheduled time is
'< 1700 then color cell red (this is during normal hours, a
signature
should be captured).
'if stop scan is false then mark as red (we should scan every stop
if
at the very least to
'get a electronic time stamp)if routescan is false then mark as red
(every route should
'be scanning right?)move each line to a separate sheet for each
terminal.

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''
With Sheets("Sheet1")
For rowCtr = 2 To totalRows
route = UCase(.Cells(rowCtr, colRoute).Value) client =
UCase(.Cells(rowCtr, colClient).Value) time =
TimeValue(.Cells(rowCtr, colTime).Value) strongBox =
UCase(.Cells(rowCtr, colSB).Value) sig = UCase(.Cells(rowCtr,
colSig).Value)

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
'check strongbox

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''
If (strongBox = "FALSE" And sbClientBool(client) And
sbRouteBool
(route)) Then
.Cells(rowCtr, colSB).Interior.ColorIndex = 3 'stop should
use strongbox
End If
If (strongBox = "TRUE" And Not sbRouteBool(route) And Not
sbClientBool(client)) Then
.Cells(rowCtr, colSB).Interior.ColorIndex = 3 'stop should
not use strongobox
End If

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''
'check stopscan

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''
If UCase(.Cells(rowCtr, colStopScan).Value) = "FALSE" Then
.Cells(rowCtr, colStopScan).Interior.ColorIndex = 3 'all
stops should be scan enabled
End If

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''
'check route scan

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
If UCase(.Cells(rowCtr, colRouteScan).Value) = "FALSE" Then
.Cells(rowCtr, colRouteScan).Interior.ColorIndex = 3 'all
routes should be scan enabled
End If

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''
'check for signature enabled stop

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''
If (sig = "TRUE" And shouldNotSigBool(time)) Then
.Cells(rowCtr, colSig).Interior.ColorIndex = 3 'after
hours,
no sig needed
End If
If (sig = "FALSE" And shouldSigBool(time)) Then
.Cells(rowCtr, colSig).Interior.ColorIndex = 3 'normal
hours,
sig needed
End If

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''
'copy row to appropiate sheet

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''
For xCtr = 1 To 9
If InStr(route, terminalNames(xCtr)) 0 Then
If xCtr = 9 Then 'ncj = nci
Sheets(terminalNames(8)).Rows(terminalRowCtr
(8)).Value = .Rows(rowCtr).Value
terminalRowCtr(8) = terminalRowCtr(8) + 1 Exit For
End If
If route = ncg100 Then 'ncg100 is a rdu route
Sheets(terminalNames(5)).Rows(terminalRowCtr
(5)).Value = .Rows(rowCtr).Value
terminalRowCtr(5) = terminalRowCtr(5) + 1 Exit For
End If
If route = nca102 Then 'nca102 is a wlk route
Sheets(terminalNames(3)).Rows(terminalRowCtr
(3)).Value = .Rows(rowCtr).Value
terminalRowCtr(3) = terminalRowCtr(3) + 1 Exit For
End If
Sheets(terminalNames(xCtr)).Rows(terminalRowCtr
(xCtr)).Value = .Rows(rowCtr).Value
terminalRowCtr(xCtr) = terminalRowCtr(xCtr) + 1 Exit
For
End If
Next xCtr
Next rowCtr
End With



'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''
'copy header row to each sheet

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''
Sheets("Sheet1").Activate
For xCtr = 1 To 8
Rows(1).Select
Selection.Copy
Sheets(terminalNames(xCtr)).Select
Selection.Insert shift:=xlDown
Sheets("Sheet1").Activate
Next xCtr
Application.ScreenUpdating = True
End Sub

Function sbClientBool(client As String) As Boolean
If UCase(client) = "BOA" Then
sbClientBool = True
Exit Function
End If
If UCase(client) = "WACHOVIA" Then
sbClientBool = True
Exit Function
End If
If UCase(client) = "SUNTRUST" Then
sbClientBool = True
Exit Function
End If
sbClientBool = False
End Function

Function sbRouteBool(route As String) As Boolean
If InStr(UCase(route), "NCR") 0 Then
sbRouteBool = True
Exit Function
End If
If InStr(UCase(route), "NCC") 0 Then
sbRouteBool = True
Exit Function
End If
If InStr(UCase(route), "NCK") 0 Then
sbRouteBool = True
Exit Function
End If
sbRouteBool = False
End Function

Function shouldSigBool(time As Date) As Boolean
Const am As Date = #5:00:00 AM#
Const pm As Date = #5:00:00 PM#
If (time am And time < pm) Then
shouldSigBool = True
Else
shouldSigBool = False
End If
End Function

Function shouldNotSigBool(time As Date) As Boolean
Const am As Date = #5:00:00 AM#
Const pm As Date = #5:00:00 PM#
If (time < am And time pm) Then
shouldNotSigBool = True
Else
shouldNotSigBool = False
End If
End Function

tia
bst


On Fri, 27 Jun 2008 13:25:00 -0700, JLGWhiz wrote:

To identify cells with conditional formatting, click
EditGoToSpecialConditional FormatsAll. They might not all be for
Interior.ColorIndex but you can tell which ones you need to check.