interior.colorindex does not work?
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 |
interior.colorindex does not work?
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 |
interior.colorindex does not work?
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 |
interior.colorindex does not work?
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 |
interior.colorindex does not work?
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 - |
interior.colorindex does not work?
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 |
interior.colorindex does not work?
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 |
interior.colorindex does not work?
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 |
interior.colorindex does not work?
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. |
interior.colorindex does not work?
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. |
interior.colorindex does not work?
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. |
interior.colorindex does not work?
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. |
All times are GMT +1. The time now is 11:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com