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.