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
To identify cells with conditional formatting, click
Edit>GoTo>Special>Conditional Formats>All. They might not all be for
Interior.ColorIndex but you can tell which ones you need to check.
