Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coloring Rows based on contents of cell A on each row
I want to create this sub or macro that will start at row 2 and cell A
test for the a word and if this is found then select the cells on that row from A to H and change the format adding color to the cells based on the word found. This is what I have so far, but it doesnt work... It runs but it doesnt change anything, I'm lost, I'm not a vba programmer but this does make sence to me, sort of... Sub color_rows() lastrow = Cells(Rows.Count, "A").End(xlUp).Row For RowCount = 2 To lastrow a_num = RowCount 'row index h_num = RowCount + 7 'cell index If Cells(RowCount) = "Open" Then ' test for the word "Open" Cells("a_num:h_num").Select ' select cells A to H in the current row With Selection.Interior ' this would change the format of the selected cells .ColorIndex = 4 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If If Cells(RowCount) = "Waiting" Then ' test for the word "Waiting" Cells("a_num:h_num").Select ' select cells A to H in the current row With Selection.Interior ' this would change the format of the selected cells .ColorIndex = 27 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If If Cells(RowCount) = "Closed" Then ' test for the word "Closed" Cells("a_num:h_num").Select ' select cells A to H in the current row Selection.Interior.ColorIndex = xlNone ' this would change the color to none End If Next RowCount End Sub If anyone see's something wrong with this, I could sure use the help... Thanks, Jeff W. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coloring Rows based on contents of cell A on each row
These won't work becaus you are only referencing the row number. You need to
include the column you want to search. expl: Cells(RowCount, 1) would search column A. If Cells(RowCount) = "Open" Then If Cells(RowCount) = "Waiting" Then "Jeff W." wrote: I want to create this sub or macro that will start at row 2 and cell A test for the a word and if this is found then select the cells on that row from A to H and change the format adding color to the cells based on the word found. This is what I have so far, but it doesnt work... It runs but it doesnt change anything, I'm lost, I'm not a vba programmer but this does make sence to me, sort of... Sub color_rows() lastrow = Cells(Rows.Count, "A").End(xlUp).Row For RowCount = 2 To lastrow a_num = RowCount 'row index h_num = RowCount + 7 'cell index If Cells(RowCount) = "Open" Then ' test for the word "Open" Cells("a_num:h_num").Select ' select cells A to H in the current row With Selection.Interior ' this would change the format of the selected cells .ColorIndex = 4 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If If Cells(RowCount) = "Waiting" Then ' test for the word "Waiting" Cells("a_num:h_num").Select ' select cells A to H in the current row With Selection.Interior ' this would change the format of the selected cells .ColorIndex = 27 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If If Cells(RowCount) = "Closed" Then ' test for the word "Closed" Cells("a_num:h_num").Select ' select cells A to H in the current row Selection.Interior.ColorIndex = xlNone ' this would change the color to none End If Next RowCount End Sub If anyone see's something wrong with this, I could sure use the help... Thanks, Jeff W. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coloring Rows based on contents of cell A on each row
I made the change you suggested but now it wont run without
Error it stops with "Type Mismatch" on the follwoing line; Cells("a_num:h_num").Select ' select cells A to H in the I have tried changing this to "Range" rather than "Cells" but the same error Any ideas? <Jeff "JLGWhiz" wrote in message ... These won't work becaus you are only referencing the row number. You need to include the column you want to search. expl: Cells(RowCount, 1) would search column A. If Cells(RowCount) = "Open" Then If Cells(RowCount) = "Waiting" Then "Jeff W." wrote: I want to create this sub or macro that will start at row 2 and cell A test for the a word and if this is found then select the cells on that row from A to H and change the format adding color to the cells based on the word found. This is what I have so far, but it doesnt work... It runs but it doesnt change anything, I'm lost, I'm not a vba programmer but this does make sence to me, sort of... Sub color_rows() lastrow = Cells(Rows.Count, "A").End(xlUp).Row For RowCount = 2 To lastrow a_num = RowCount 'row index h_num = RowCount + 7 'cell index If Cells(RowCount) = "Open" Then ' test for the word "Open" Cells("a_num:h_num").Select ' select cells A to H in the current row With Selection.Interior ' this would change the format of the selected cells .ColorIndex = 4 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If If Cells(RowCount) = "Waiting" Then ' test for the word "Waiting" Cells("a_num:h_num").Select ' select cells A to H in the current row With Selection.Interior ' this would change the format of the selected cells .ColorIndex = 27 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If If Cells(RowCount) = "Closed" Then ' test for the word "Closed" Cells("a_num:h_num").Select ' select cells A to H in the current row Selection.Interior.ColorIndex = xlNone ' this would change the color to none End If Next RowCount End Sub If anyone see's something wrong with this, I could sure use the help... Thanks, Jeff W. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coloring Rows based on contents of cell A on each row
On Oct 17, 9:35 pm, "Jeff W." wrote:
I made the change you suggested but now it wont run without Error it stops with "Type Mismatch" on the follwoing line; Cells("a_num:h_num").Select ' select cells A to H in the I have tried changing this to "Range" rather than "Cells" but the same error Any ideas? <Jeff "JLGWhiz" wrote in message ... These won't work becaus you are only referencing the row number. You need to include the column you want to search. expl: Cells(RowCount, 1) would search column A. If Cells(RowCount) = "Open" Then If Cells(RowCount) = "Waiting" Then "Jeff W." wrote: I want to create this sub or macro that will start at row 2 and cell A test for the a word and if this is found then select the cells on that row from A to H and change the format adding color to the cells based on the word found. This is what I have so far, but it doesnt work... It runs but it doesnt change anything, I'm lost, I'm not a vba programmer but this does make sence to me, sort of... Sub color_rows() lastrow = Cells(Rows.Count, "A").End(xlUp).Row For RowCount = 2 To lastrow a_num = RowCount 'row index h_num = RowCount + 7 'cell index If Cells(RowCount) = "Open" Then ' test for the word "Open" Cells("a_num:h_num").Select ' select cells A to H in the current row With Selection.Interior ' this would change the format of the selected cells .ColorIndex = 4 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If If Cells(RowCount) = "Waiting" Then ' test for the word "Waiting" Cells("a_num:h_num").Select ' select cells A to H in the current row With Selection.Interior ' this would change the format of the selected cells .ColorIndex = 27 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If If Cells(RowCount) = "Closed" Then ' test for the word "Closed" Cells("a_num:h_num").Select ' select cells A to H in the current row Selection.Interior.ColorIndex = xlNone ' this would change the color to none End If Next RowCount End Sub If anyone see's something wrong with this, I could sure use the help... Thanks, Jeff W. Keep in mind, you don't have anything controlling if the cells are already colored. Might want to clear the colorindex of the rows at the top of the code to ensure that everything is formatted correctly. Sub color_rows() Dim lastrow As Long, rowcount As Long lastrow = Cells(Rows.Count, "A").End(xlUp).Row For rowcount = 2 To lastrow With Range(Cells(rowcount, 1), _ Cells(rowcount, 8)).Interior If Cells(rowcount, 1).Value = _ "Open" Then .ColorIndex = 4 ElseIf Cells(rowcount, 1) = _ "Waiting" Then .ColorIndex = 27 ElseIf Cells(rowcount, 1) = _ "Closed" Then .ColorIndex = xlNone End If End With Next rowcount End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coloring Rows based on contents of cell A on each row
On Oct 17, 9:35 pm, "Jeff W." wrote:
I made the change you suggested but now it wont run without Error it stops with "Type Mismatch" on the follwoing line; Cells("a_num:h_num").Select ' select cells A to H in the I have tried changing this to "Range" rather than "Cells" but the same error Any ideas? <Jeff "JLGWhiz" wrote in message ... These won't work becaus you are only referencing the row number. You need to include the column you want to search. expl: Cells(RowCount, 1) would search column A. If Cells(RowCount) = "Open" Then If Cells(RowCount) = "Waiting" Then "Jeff W." wrote: I want to create this sub or macro that will start at row 2 and cell A test for the a word and if this is found then select the cells on that row from A to H and change the format adding color to the cells based on the word found. This is what I have so far, but it doesnt work... It runs but it doesnt change anything, I'm lost, I'm not a vba programmer but this does make sence to me, sort of... Sub color_rows() lastrow = Cells(Rows.Count, "A").End(xlUp).Row For RowCount = 2 To lastrow a_num = RowCount 'row index h_num = RowCount + 7 'cell index If Cells(RowCount) = "Open" Then ' test for the word "Open" Cells("a_num:h_num").Select ' select cells A to H in the current row With Selection.Interior ' this would change the format of the selected cells .ColorIndex = 4 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If If Cells(RowCount) = "Waiting" Then ' test for the word "Waiting" Cells("a_num:h_num").Select ' select cells A to H in the current row With Selection.Interior ' this would change the format of the selected cells .ColorIndex = 27 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If If Cells(RowCount) = "Closed" Then ' test for the word "Closed" Cells("a_num:h_num").Select ' select cells A to H in the current row Selection.Interior.ColorIndex = xlNone ' this would change the color to none End If Next RowCount End Sub If anyone see's something wrong with this, I could sure use the help... Thanks, Jeff W. Keep in mind, you don't have anything controlling if the cells are already colored. Might want to clear the colorindex of the rows at the top of the code to ensure that everything is formatted correctly. Notice that there are no selections in the code. In general, unnecessary selections is considered bad coding practice and should be avoided if at all possible. Sub color_rows() Dim lastrow As Long, rowcount As Long lastrow = Cells(Rows.Count, "A").End(xlUp).Row For rowcount = 2 To lastrow With Range(Cells(rowcount, 1), _ Cells(rowcount, 8)).Interior If Cells(rowcount, 1).Value = _ "Open" Then .ColorIndex = 4 ElseIf Cells(rowcount, 1) = _ "Waiting" Then .ColorIndex = 27 ElseIf Cells(rowcount, 1) = _ "Closed" Then .ColorIndex = xlNone End If End With Next rowcount End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coloring Rows based on contents of cell A on each row
Works great!
The colors need to change based on the word changing so I dont know if it can make any difference whether they are already colored. I know from programming in other applications that there are usually more than one way to get someing done, what you have done here is certainly cleaner and more compact than my method. Thanks... Jeff W. "JW" wrote in message ups.com... On Oct 17, 9:35 pm, "Jeff W." wrote: I made the change you suggested but now it wont run without Error it stops with "Type Mismatch" on the follwoing line; Cells("a_num:h_num").Select ' select cells A to H in the I have tried changing this to "Range" rather than "Cells" but the same error Any ideas? <Jeff "JLGWhiz" wrote in message ... These won't work becaus you are only referencing the row number. You need to include the column you want to search. expl: Cells(RowCount, 1) would search column A. If Cells(RowCount) = "Open" Then If Cells(RowCount) = "Waiting" Then "Jeff W." wrote: I want to create this sub or macro that will start at row 2 and cell A test for the a word and if this is found then select the cells on that row from A to H and change the format adding color to the cells based on the word found. This is what I have so far, but it doesnt work... It runs but it doesnt change anything, I'm lost, I'm not a vba programmer but this does make sence to me, sort of... Sub color_rows() lastrow = Cells(Rows.Count, "A").End(xlUp).Row For RowCount = 2 To lastrow a_num = RowCount 'row index h_num = RowCount + 7 'cell index If Cells(RowCount) = "Open" Then ' test for the word "Open" Cells("a_num:h_num").Select ' select cells A to H in the current row With Selection.Interior ' this would change the format of the selected cells .ColorIndex = 4 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If If Cells(RowCount) = "Waiting" Then ' test for the word "Waiting" Cells("a_num:h_num").Select ' select cells A to H in the current row With Selection.Interior ' this would change the format of the selected cells .ColorIndex = 27 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If If Cells(RowCount) = "Closed" Then ' test for the word "Closed" Cells("a_num:h_num").Select ' select cells A to H in the current row Selection.Interior.ColorIndex = xlNone ' this would change the color to none End If Next RowCount End Sub If anyone see's something wrong with this, I could sure use the help... Thanks, Jeff W. Keep in mind, you don't have anything controlling if the cells are already colored. Might want to clear the colorindex of the rows at the top of the code to ensure that everything is formatted correctly. Notice that there are no selections in the code. In general, unnecessary selections is considered bad coding practice and should be avoided if at all possible. Sub color_rows() Dim lastrow As Long, rowcount As Long lastrow = Cells(Rows.Count, "A").End(xlUp).Row For rowcount = 2 To lastrow With Range(Cells(rowcount, 1), _ Cells(rowcount, 8)).Interior If Cells(rowcount, 1).Value = _ "Open" Then .ColorIndex = 4 ElseIf Cells(rowcount, 1) = _ "Waiting" Then .ColorIndex = 27 ElseIf Cells(rowcount, 1) = _ "Closed" Then .ColorIndex = xlNone End If End With Next rowcount End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coloring Rows based on contents of cell A on each row
You're welcome Jeff. I would still recommend clearing the cell color
index before the loop. What is a record that was previously "Open" now has something like "Close" (Closed without the D). That row would remain colored like the "Open" rows, but it truly isn't. Better safe than sorry. To clear all color index values of all rows except the header row, use something like below: Sub clearColorIndex() Rows("2:65536").Interior.ColorIndex = xlNone 'or Rows("2:" & Rows.Count).Interior.ColorIndex = xlNone End Sub Jeff W. wrote: Works great! The colors need to change based on the word changing so I dont know if it can make any difference whether they are already colored. I know from programming in other applications that there are usually more than one way to get someing done, what you have done here is certainly cleaner and more compact than my method. Thanks... Jeff W. "JW" wrote in message ups.com... On Oct 17, 9:35 pm, "Jeff W." wrote: I made the change you suggested but now it wont run without Error it stops with "Type Mismatch" on the follwoing line; Cells("a_num:h_num").Select ' select cells A to H in the I have tried changing this to "Range" rather than "Cells" but the same error Any ideas? <Jeff "JLGWhiz" wrote in message ... These won't work becaus you are only referencing the row number. You need to include the column you want to search. expl: Cells(RowCount, 1) would search column A. If Cells(RowCount) = "Open" Then If Cells(RowCount) = "Waiting" Then "Jeff W." wrote: I want to create this sub or macro that will start at row 2 and cell A test for the a word and if this is found then select the cells on that row from A to H and change the format adding color to the cells based on the word found. This is what I have so far, but it doesnt work... It runs but it doesnt change anything, I'm lost, I'm not a vba programmer but this does make sence to me, sort of... Sub color_rows() lastrow = Cells(Rows.Count, "A").End(xlUp).Row For RowCount = 2 To lastrow a_num = RowCount 'row index h_num = RowCount + 7 'cell index If Cells(RowCount) = "Open" Then ' test for the word "Open" Cells("a_num:h_num").Select ' select cells A to H in the current row With Selection.Interior ' this would change the format of the selected cells .ColorIndex = 4 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If If Cells(RowCount) = "Waiting" Then ' test for the word "Waiting" Cells("a_num:h_num").Select ' select cells A to H in the current row With Selection.Interior ' this would change the format of the selected cells .ColorIndex = 27 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If If Cells(RowCount) = "Closed" Then ' test for the word "Closed" Cells("a_num:h_num").Select ' select cells A to H in the current row Selection.Interior.ColorIndex = xlNone ' this would change the color to none End If Next RowCount End Sub If anyone see's something wrong with this, I could sure use the help... Thanks, Jeff W. Keep in mind, you don't have anything controlling if the cells are already colored. Might want to clear the colorindex of the rows at the top of the code to ensure that everything is formatted correctly. Notice that there are no selections in the code. In general, unnecessary selections is considered bad coding practice and should be avoided if at all possible. Sub color_rows() Dim lastrow As Long, rowcount As Long lastrow = Cells(Rows.Count, "A").End(xlUp).Row For rowcount = 2 To lastrow With Range(Cells(rowcount, 1), _ Cells(rowcount, 8)).Interior If Cells(rowcount, 1).Value = _ "Open" Then .ColorIndex = 4 ElseIf Cells(rowcount, 1) = _ "Waiting" Then .ColorIndex = 27 ElseIf Cells(rowcount, 1) = _ "Closed" Then .ColorIndex = xlNone End If End With Next rowcount End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coloring Rows based on contents of cell A on each row
Thanks, I'll do that...
<Jeff "JW" wrote in message ps.com... You're welcome Jeff. I would still recommend clearing the cell color index before the loop. What is a record that was previously "Open" now has something like "Close" (Closed without the D). That row would remain colored like the "Open" rows, but it truly isn't. Better safe than sorry. To clear all color index values of all rows except the header row, use something like below: Sub clearColorIndex() Rows("2:65536").Interior.ColorIndex = xlNone 'or Rows("2:" & Rows.Count).Interior.ColorIndex = xlNone End Sub Jeff W. wrote: Works great! The colors need to change based on the word changing so I dont know if it can make any difference whether they are already colored. I know from programming in other applications that there are usually more than one way to get someing done, what you have done here is certainly cleaner and more compact than my method. Thanks... Jeff W. "JW" wrote in message ups.com... On Oct 17, 9:35 pm, "Jeff W." wrote: I made the change you suggested but now it wont run without Error it stops with "Type Mismatch" on the follwoing line; Cells("a_num:h_num").Select ' select cells A to H in the I have tried changing this to "Range" rather than "Cells" but the same error Any ideas? <Jeff "JLGWhiz" wrote in message ... These won't work becaus you are only referencing the row number. You need to include the column you want to search. expl: Cells(RowCount, 1) would search column A. If Cells(RowCount) = "Open" Then If Cells(RowCount) = "Waiting" Then "Jeff W." wrote: I want to create this sub or macro that will start at row 2 and cell A test for the a word and if this is found then select the cells on that row from A to H and change the format adding color to the cells based on the word found. This is what I have so far, but it doesnt work... It runs but it doesnt change anything, I'm lost, I'm not a vba programmer but this does make sence to me, sort of... Sub color_rows() lastrow = Cells(Rows.Count, "A").End(xlUp).Row For RowCount = 2 To lastrow a_num = RowCount 'row index h_num = RowCount + 7 'cell index If Cells(RowCount) = "Open" Then ' test for the word "Open" Cells("a_num:h_num").Select ' select cells A to H in the current row With Selection.Interior ' this would change the format of the selected cells .ColorIndex = 4 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If If Cells(RowCount) = "Waiting" Then ' test for the word "Waiting" Cells("a_num:h_num").Select ' select cells A to H in the current row With Selection.Interior ' this would change the format of the selected cells .ColorIndex = 27 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If If Cells(RowCount) = "Closed" Then ' test for the word "Closed" Cells("a_num:h_num").Select ' select cells A to H in the current row Selection.Interior.ColorIndex = xlNone ' this would change the color to none End If Next RowCount End Sub If anyone see's something wrong with this, I could sure use the help... Thanks, Jeff W. Keep in mind, you don't have anything controlling if the cells are already colored. Might want to clear the colorindex of the rows at the top of the code to ensure that everything is formatted correctly. Notice that there are no selections in the code. In general, unnecessary selections is considered bad coding practice and should be avoided if at all possible. Sub color_rows() Dim lastrow As Long, rowcount As Long lastrow = Cells(Rows.Count, "A").End(xlUp).Row For rowcount = 2 To lastrow With Range(Cells(rowcount, 1), _ Cells(rowcount, 8)).Interior If Cells(rowcount, 1).Value = _ "Open" Then .ColorIndex = 4 ElseIf Cells(rowcount, 1) = _ "Waiting" Then .ColorIndex = 27 ElseIf Cells(rowcount, 1) = _ "Closed" Then .ColorIndex = xlNone End If End With Next rowcount End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hiding rows based on cell contents | Excel Discussion (Misc queries) | |||
VBA Code to Group Rows Based on Cell Contents | Excel Programming | |||
VBA Code to Group Rows Based on Cell Contents | Excel Programming | |||
Add Rows based on Cell contents | Excel Discussion (Misc queries) | |||
Coloring Cell Font based on Cell Contents | Excel Programming |