| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
color row using macro..
Hi,
Is there any way to use a macro to find a word example "Total" in a column example "C:C" and when it finds the word it will color that row say "Blue" and then find next and if find more do the same all the way to the end of the column. Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
color row using macro..
Hi,
Right click your sheet tab, view code and paste this in and run it Sub marine() Dim myrange, MyRange1 As Range lastrow = Cells(Rows.Count, "C").End(xlUp).Row Set myrange = Range("C1:C" & lastrow) For Each c In myrange If UCase(c.Value) = "TOTAL" Then If MyRange1 Is Nothing Then Set MyRange1 = c.EntireRow Else Set MyRange1 = Union(MyRange1, c.EntireRow) End If End If Next MyRange1.Interior.ColorIndex = 41 End Sub Mike " wrote: Hi, Is there any way to use a macro to find a word example "Total" in a column example "C:C" and when it finds the word it will color that row say "Blue" and then find next and if find more do the same all the way to the end of the column. Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
color row using macro..
try this. change sheet25 to suit the name of your sheet and 500 to suit your
last row Sub colortotalrow() With Worksheets("sheet25").Range("c1:c500") Set c = .Find("Total", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Rows(c.Row).Interior.ColorIndex = 6 Set c = .FindNext(c) Loop While Not c Is Nothing _ And c.Address < firstAddress End If End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... Hi, Is there any way to use a macro to find a word example "Total" in a column example "C:C" and when it finds the word it will color that row say "Blue" and then find next and if find more do the same all the way to the end of the column. Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
color row using macro..
On Jun 21, 6:45*am, "Don Guillett" wrote:
try this. change sheet25 to suit the name of your sheet and 500 to suit your last row Sub colortotalrow() With Worksheets("sheet25").Range("c1:c500") * * Set c = .Find("Total", LookIn:=xlValues) * * If Not c Is Nothing Then * * * * firstAddress = c.Address * * * * Do * * * * *Rows(c.Row).Interior.ColorIndex = 6 * * * * Set c = .FindNext(c) * * * * Loop While Not c Is Nothing _ * * * * And c.Address < firstAddress * * End If End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... Hi, Is there any way to use a macro to find a word example "Total" in a column example "C:C" and when it finds the word it will color that row say "Blue" and then find next and if find more do the same all the way to the end of the column. Thanks- Hide quoted text - - Show quoted text - Hi, Thanks it works great. Can I make another macro or add to this one, I need the macro to first find the "Total" in "C:C" then clear the Borders in that ROW and then place a border on top and bottom of that ROW and if any cell in that ROW is not empty place a OUTLINE Border. Thanks |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
color row using macro..
On Jun 21, 10:19*am, wrote:
On Jun 21, 6:45*am, "Don Guillett" wrote: try this. change sheet25 to suit the name of your sheet and 500 to suit your last row Sub colortotalrow() With Worksheets("sheet25").Range("c1:c500") * * Set c = .Find("Total", LookIn:=xlValues) * * If Not c Is Nothing Then * * * * firstAddress = c.Address * * * * Do * * * * *Rows(c.Row).Interior.ColorIndex = 6 * * * * Set c = .FindNext(c) * * * * Loop While Not c Is Nothing _ * * * * And c.Address < firstAddress * * End If End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... Hi, Is there any way to use a macro to find a word example "Total" in a column example "C:C" and when it finds the word it will color that row say "Blue" and then find next and if find more do the same all the way to the end of the column. Hi, Thanks it works great. Can I make another macro or add to this one, I need the macro to first find the "Total" in "C:C" then clear the Borders in that ROW and then place a border on top and bottom of that ROW and if any cell in that ROW is not empty place a OUTLINE Border. Thanks |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
color row using macro..
Did you NOT see my last post with the change and the request to TOP POST
-- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... On Jun 21, 10:19 am, wrote: On Jun 21, 6:45 am, "Don Guillett" wrote: try this. change sheet25 to suit the name of your sheet and 500 to suit your last row Sub colortotalrow() With Worksheets("sheet25").Range("c1:c500") Set c = .Find("Total", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Rows(c.Row).Interior.ColorIndex = 6 Set c = .FindNext(c) Loop While Not c Is Nothing _ And c.Address < firstAddress End If End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... Hi, Is there any way to use a macro to find a word example "Total" in a column example "C:C" and when it finds the word it will color that row say "Blue" and then find next and if find more do the same all the way to the end of the column. Hi, Thanks it works great. Can I make another macro or add to this one, I need the macro to first find the "Total" in "C:C" then clear the Borders in that ROW and then place a border on top and bottom of that ROW and if any cell in that ROW is not empty place a OUTLINE Border. Thanks |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
color row using macro..
On Jun 21, 11:38*am, "Don Guillett" wrote:
Did you NOT see my last post with the change and the request to TOP POST -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... On Jun 21, 10:19 am, wrote: On Jun 21, 6:45 am, "Don Guillett" wrote: try this. change sheet25 to suit the name of your sheet and 500 to suit your last row Sub colortotalrow() With Worksheets("sheet25").Range("c1:c500") Set c = .Find("Total", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Rows(c.Row).Interior.ColorIndex = 6 Set c = .FindNext(c) Loop While Not c Is Nothing _ And c.Address < firstAddress End If End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message .... Hi, Is there any way to use a macro to find a word example "Total" in a column example "C:C" and when it finds the word it will color that row say "Blue" and then find next and if find more do the same all the way to the end of the column. Hi, Thanks it works great. Can I make another macro or add to this one, I need the macro to first find the "Total" in "C:C" then clear the Borders in that ROW and then place a border on top and bottom of that ROW and if any cell in that ROW is not empty place a OUTLINE Border. Thanks- Hide quoted text - - Show quoted text - Hi, What is a top post? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
color row using macro..
On Jun 21, 11:38*am, "Don Guillett" wrote:
Did you NOT see my last post with the change and the request to TOP POST -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... On Jun 21, 10:19 am, wrote: On Jun 21, 6:45 am, "Don Guillett" wrote: try this. change sheet25 to suit the name of your sheet and 500 to suit your last row Sub colortotalrow() With Worksheets("sheet25").Range("c1:c500") Set c = .Find("Total", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Rows(c.Row).Interior.ColorIndex = 6 Set c = .FindNext(c) Loop While Not c Is Nothing _ And c.Address < firstAddress End If End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message .... Hi, Is there any way to use a macro to find a word example "Total" in a column example "C:C" and when it finds the word it will color that row say "Blue" and then find next and if find more do the same all the way to the end of the column. Hi, Thanks it works great. Can I make another macro or add to this one, I need the macro to first find the "Total" in "C:C" then clear the Borders in that ROW and then place a border on top and bottom of that ROW and if any cell in that ROW is not empty place a OUTLINE Border. Thanks- Hide quoted text - - Show quoted text - Yes, I see know. Thanks. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
color row using macro..
On Jun 21, 2:09*pm, wrote:
On Jun 21, 11:38*am, "Don Guillett" wrote: Did you NOT see my last post with the change and the request to TOP POST -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... On Jun 21, 10:19 am, wrote: On Jun 21, 6:45 am, "Don Guillett" wrote: try this. change sheet25 to suit the name of your sheet and 500 to suit your last row Sub colortotalrow() With Worksheets("sheet25").Range("c1:c500") Set c = .Find("Total", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Rows(c.Row).Interior.ColorIndex = 6 Set c = .FindNext(c) Loop While Not c Is Nothing _ And c.Address < firstAddress End If End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... Hi, Is there any way to use a macro to find a word example "Total" in a column example "C:C" and when it finds the word it will color that row say "Blue" and then find next and if find more do the same all the way to the end of the column. Hi, Thanks it works great. Can I make another macro or add to this one, I need the macro to first find the "Total" in "C:C" then clear the Borders in that ROW and then place a border on top and bottom of that ROW and if any cell in that ROW is not empty place a OUTLINE Border. Thanks- Hide quoted text - - Show quoted text - Yes, I see know. Thanks.- Hide quoted text - - Show quoted text - Hi, Thanks, How about if any cell in that ROW that we colored that is not empty place a OUTLINE Border, not only the top and bottom but the sides too for those cells. Thanks |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
color row using macro..
Top post is when you do NOT keep putting your new messages at the bottom of
the old messages like you have been doing; rather, it is where you put you message at the top of stack of previous messages like this response from me did to your question (previous message). The reason volunteers like top posting is they don't have to scroll down a "mile's worth" of previous messages just to find the new message you have added. If you top post, then your message is right on top... easy to find and easy to respond to. Rick What is a top post? |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
color row using macro..
Thanks!
On Jun 21, 2:45*pm, "Rick Rothstein \(MVP - VB\)" wrote: Top post is when you do NOT keep putting your new messages at the bottom of the old messages like you have been doing; rather, it is where you put you message at the top of stack of previous messages like this response from me did to your question (previous message). The reason volunteers like top posting is they don't have to scroll down a "mile's worth" of previous messages just to find the new message you have added. If you top post, then your message is right on top... easy to find and easy to respond to. Rick What is a top post?- Hide quoted text - - Show quoted text - |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
color row using macro..
Hi,
Thanks, Now I got the Top Post! How about if any cell in that ROW that we colored that is not empty place a OUTLINE Border, not only the top and bottom but the sides too for those cells that are not empty. Thanks. On Jun 21, 11:38*am, "Don Guillett" wrote: Did you NOT see my last post with the change and the request to TOP POST -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... On Jun 21, 10:19 am, wrote: On Jun 21, 6:45 am, "Don Guillett" wrote: try this. change sheet25 to suit the name of your sheet and 500 to suit your last row Sub colortotalrow() With Worksheets("sheet25").Range("c1:c500") Set c = .Find("Total", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Rows(c.Row).Interior.ColorIndex = 6 Set c = .FindNext(c) Loop While Not c Is Nothing _ And c.Address < firstAddress End If End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message .... Hi, Is there any way to use a macro to find a word example "Total" in a column example "C:C" and when it finds the word it will color that row say "Blue" and then find next and if find more do the same all the way to the end of the column. Hi, Thanks it works great. Can I make another macro or add to this one, I need the macro to first find the "Total" in "C:C" then clear the Borders in that ROW and then place a border on top and bottom of that ROW and if any cell in that ROW is not empty place a OUTLINE Border. Thanks- Hide quoted text - - Show quoted text - |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
color row using macro..
What you want can be done. Let's see now
we have an empty cell to the right we have a full cell to the right we have an empty cell so now all cells have a right and left border also. Tell us how to NOT have a border on the empty cell in the middle. You did not mention your layout or how many cells per row may be involved. If desired, send me a workbook to my address below and I'll have a look. -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... Hi, Thanks, Now I got the Top Post! How about if any cell in that ROW that we colored that is not empty place a OUTLINE Border, not only the top and bottom but the sides too for those cells that are not empty. Thanks. On Jun 21, 11:38 am, "Don Guillett" wrote: Did you NOT see my last post with the change and the request to TOP POST -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... On Jun 21, 10:19 am, wrote: On Jun 21, 6:45 am, "Don Guillett" wrote: try this. change sheet25 to suit the name of your sheet and 500 to suit your last row Sub colortotalrow() With Worksheets("sheet25").Range("c1:c500") Set c = .Find("Total", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Rows(c.Row).Interior.ColorIndex = 6 Set c = .FindNext(c) Loop While Not c Is Nothing _ And c.Address < firstAddress End If End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... Hi, Is there any way to use a macro to find a word example "Total" in a column example "C:C" and when it finds the word it will color that row say "Blue" and then find next and if find more do the same all the way to the end of the column. Hi, Thanks it works great. Can I make another macro or add to this one, I need the macro to first find the "Total" in "C:C" then clear the Borders in that ROW and then place a border on top and bottom of that ROW and if any cell in that ROW is not empty place a OUTLINE Border. Thanks- Hide quoted text - - Show quoted text - |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
color row using macro..
I sent this
Sub ColorTotalRow() Application.ScreenUpdating = False With Worksheets("sheet26") With Cells.SpecialCells(xlCellTypeLastCell) lc = .Column lr = .Row End With With .Rows("2:" & lr) ..Font.Bold = False ..Borders.LineStyle = xlNone ..Interior.ColorIndex = xlNone End With With .Range("c2:c" & lr) Set c = .Find("Total", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do With Range(Cells(c.Row, 1), Cells(c.Row, lc)) .Font.Bold = True .Interior.ColorIndex = 6 .BorderAround , Weight:=xlMedium End With For Each cc In Range(Cells(c.Row, 1), Cells(c.Row, lc)) If Len(Application.Trim(cc)) 0 Then cc.Borders.LineStyle = xlContinuous cc.Borders.Weight = xlMedium End If Next cc Set c = .FindNext(c) Loop While Not c Is Nothing _ And c.Address < firstAddress End If End With End With Application.ScreenUpdating = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... What you want can be done. Let's see now we have an empty cell to the right we have a full cell to the right we have an empty cell so now all cells have a right and left border also. Tell us how to NOT have a border on the empty cell in the middle. You did not mention your layout or how many cells per row may be involved. If desired, send me a workbook to my address below and I'll have a look. -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... Hi, Thanks, Now I got the Top Post! How about if any cell in that ROW that we colored that is not empty place a OUTLINE Border, not only the top and bottom but the sides too for those cells that are not empty. Thanks. On Jun 21, 11:38 am, "Don Guillett" wrote: Did you NOT see my last post with the change and the request to TOP POST -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... On Jun 21, 10:19 am, wrote: On Jun 21, 6:45 am, "Don Guillett" wrote: try this. change sheet25 to suit the name of your sheet and 500 to suit your last row Sub colortotalrow() With Worksheets("sheet25").Range("c1:c500") Set c = .Find("Total", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Rows(c.Row).Interior.ColorIndex = 6 Set c = .FindNext(c) Loop While Not c Is Nothing _ And c.Address < firstAddress End If End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... Hi, Is there any way to use a macro to find a word example "Total" in a column example "C:C" and when it finds the word it will color that row say "Blue" and then find next and if find more do the same all the way to the end of the column. Hi, Thanks it works great. Can I make another macro or add to this one, I need the macro to first find the "Total" in "C:C" then clear the Borders in that ROW and then place a border on top and bottom of that ROW and if any cell in that ROW is not empty place a OUTLINE Border. Thanks- Hide quoted text - - Show quoted text - |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
color row using macro..
Thanks, It is Great!
On Jun 22, 6:48*am, "Don Guillett" wrote: I sent this Sub ColorTotalRow() Application.ScreenUpdating = False With Worksheets("sheet26") With Cells.SpecialCells(xlCellTypeLastCell) lc = .Column lr = .Row End With With .Rows("2:" & lr) .Font.Bold = False .Borders.LineStyle = xlNone .Interior.ColorIndex = xlNone End With With .Range("c2:c" & lr) * * Set c = .Find("Total", LookIn:=xlValues) * * If Not c Is Nothing Then * * * * firstAddress = c.Address * * * Do * * * * With Range(Cells(c.Row, 1), Cells(c.Row, lc)) * * * * * .Font.Bold = True * * * * * .Interior.ColorIndex = 6 * * * * * .BorderAround , Weight:=xlMedium * * * * End With * * * For Each cc In Range(Cells(c.Row, 1), Cells(c.Row, lc)) * * * *If Len(Application.Trim(cc)) 0 Then * * * * cc.Borders.LineStyle = xlContinuous * * * * cc.Borders.Weight = xlMedium * * * *End If * * * Next cc * * * Set c = .FindNext(c) * * * Loop While Not c Is Nothing _ * * * And c.Address < firstAddress * * End If End With End With Application.ScreenUpdating = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... What you want can be done. Let's see now we have an empty cell to the right we have a full cell to the right we have an empty cell so now all cells have a right and leftborderalso. Tell us how to NOT have aborderon the empty cell in the middle. You did not mention your layout or how many cells per row may be involved. If desired, send me a workbook to my address below and I'll have a look.. -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message .... Hi, Thanks, Now I got the Top Post! How about if any cell in that ROW that we colored that is not empty place a OUTLINEBorder, not only the top and bottom but the sides too for those cells that are not empty. Thanks. On Jun 21, 11:38 am, "Don Guillett" wrote: Did you NOT see my last post with the change and the request to TOP POST -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message .... On Jun 21, 10:19 am, wrote: On Jun 21, 6:45 am, "Don Guillett" wrote: try this. change sheet25 to suit the name of your sheet and 500 to suit your last row Sub colortotalrow() With Worksheets("sheet25").Range("c1:c500") Set c = .Find("Total", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Rows(c.Row).Interior.ColorIndex = 6 Set c = .FindNext(c) Loop While Not c Is Nothing _ And c.Address < firstAddress End If End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... Hi, Is there any way to use a macro to find a word example "Total" in a column example "C:C" and when it finds the word it will color that row say "Blue" and then find next and if find more do the same all the way to the end of the column. Hi, Thanks it works great. Can I make another macro or add to this one, I need the macro to first find the "Total" in "C:C" then clear the Borders in that ROW and then place aborderon top and bottom of that ROW and if any cell in that ROW is not empty place a OUTLINEBorder. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
color row using macro..
|
|
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
color row using macro..
Hi,
Something else came up, hope you can help! Can we add a line to say that when it finds the "Total" also insert a ROW below that Row Thanks On Jun 22, 3:26*pm, "Don Guillett" wrote: glad to help -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... Thanks, It is Great! On Jun 22, 6:48 am, "Don Guillett" wrote: I sent this SubColorTotalRow() Application.ScreenUpdating = False With Worksheets("sheet26") With Cells.SpecialCells(xlCellTypeLastCell) lc = .Column lr = .Row End With With .Rows("2:" & lr) .Font.Bold = False .Borders.LineStyle = xlNone .Interior.ColorIndex = xlNone End With With .Range("c2:c" & lr) Set c = .Find("Total", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do With Range(Cells(c.Row, 1), Cells(c.Row, lc)) .Font.Bold = True .Interior.ColorIndex = 6 .BorderAround , Weight:=xlMedium End With For Each cc In Range(Cells(c.Row, 1), Cells(c.Row, lc)) If Len(Application.Trim(cc)) 0 Then cc.Borders.LineStyle = xlContinuous cc.Borders.Weight = xlMedium End If Next cc Set c = .FindNext(c) Loop While Not c Is Nothing _ And c.Address < firstAddress End If End With End With Application.ScreenUpdating = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... What you want can be done. Let's see now we have an empty cell to the right we have a full cell to the right we have an empty cell so now all cells have a right and leftborderalso. Tell us how to NOT have aborderon the empty cell in the middle. You did not mention your layout or how many cells per row may be involved. If desired, send me a workbook to my address below and I'll have a look. -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message .... Hi, Thanks, Now I got the Top Post! How about if any cell in that ROW that we colored that is not empty place a OUTLINEBorder, not only the top and bottom but the sides too for those cells that are not empty. Thanks. On Jun 21, 11:38 am, "Don Guillett" wrote: Did you NOT see my last post with the change and the request to TOP POST -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message .... On Jun 21, 10:19 am, wrote: On Jun 21, 6:45 am, "Don Guillett" wrote: try this. change sheet25 to suit the name of your sheet and 500 to suit your last row Subcolortotalrow() With Worksheets("sheet25").Range("c1:c500") Set c = .Find("Total", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Rows(c.Row).Interior.ColorIndex = 6 Set c = .FindNext(c) Loop While Not c Is Nothing _ And c.Address < firstAddress End If End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... Hi, Is there any way to use a macro to find a word example "Total" in a column example "C:C" and when it finds the word it will color that row say "Blue" and then find next and if find more do the same all the way to the end of the column. Hi, Thanks it works great. Can I make another macro or add to this one, I need the macro to first find the "Total" in "C:C" then clear the Borders in that ROW and then place aborderon top and bottom of that ROW and if any cell in that ROW is not empty place a OUTLINEBorder. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
color row using macro..
|
|
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
color row using macro..
Hi, Thanks.
How can I make the statement below to only do the Borders to cells that are not empty, cells that have no values should not have borders. cc.Borders.LineStyle = xlContinuous Thanks. On Jul 6, 6:44*am, "Don Guillett" wrote: Just add another line below the DO ' Do *Rows(c.row + 1).Insert -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... Hi, Something else came up, hope you can help! Can we add a line to say that when it finds the "Total" also insert a ROW below that Row Thanks On Jun 22, 3:26 pm, "Don Guillett" wrote: glad to help -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... Thanks, It is Great! On Jun 22, 6:48 am, "Don Guillett" wrote: I sent this SubColorTotalRow() Application.ScreenUpdating = False With Worksheets("sheet26") With Cells.SpecialCells(xlCellTypeLastCell) lc = .Column lr = .Row End With With .Rows("2:" & lr) .Font.Bold = False .Borders.LineStyle = xlNone .Interior.ColorIndex = xlNone End With With .Range("c2:c" & lr) Set c = .Find("Total", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do With Range(Cells(c.Row, 1), Cells(c.Row, lc)) .Font.Bold = True .Interior.ColorIndex = 6 .BorderAround , Weight:=xlMedium End With For Each cc In Range(Cells(c.Row, 1), Cells(c.Row, lc)) If Len(Application.Trim(cc)) 0 Then cc.Borders.LineStyle = xlContinuous cc.Borders.Weight = xlMedium End If Next cc Set c = .FindNext(c) Loop While Not c Is Nothing _ And c.Address < firstAddress End If End With End With Application.ScreenUpdating = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... What you want can be done. Let's see now we have an empty cell to the right we have a full cell to the right we have an empty cell so now all cells have a right and leftborderalso. Tell us how to NOT have aborderon the empty cell in the middle. You did not mention your layout or how many cells per row may be involved. If desired, send me a workbook to my address below and I'll have a look. -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... Hi, Thanks, Now I got the Top Post! How about if any cell in that ROW that we colored that is not empty place a OUTLINEBorder, not only the top and bottom but the sides too for those cells that are not empty. Thanks. On Jun 21, 11:38 am, "Don Guillett" wrote: Did you NOT see my last post with the change and the request to TOP POST -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... On Jun 21, 10:19 am, wrote: On Jun 21, 6:45 am, "Don Guillett" wrote: try this. change sheet25 to suit the name of your sheet and 500 to suit your last row Subcolortotalrow() With Worksheets("sheet25").Range("c1:c500") Set c = .Find("Total", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Rows(c.Row).Interior.ColorIndex = 6 Set c = .FindNext(c) Loop While Not c Is Nothing _ And c.Address < firstAddress End If End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... Hi, Is there any way to use a macro to find a word example "Total" in a column example "C:C" and when it finds the word it will color that row say "Blue" and then find next and if find more do the same all the way to the end of the column. Hi, Thanks it works great. Can I make another macro or add to this one, I need the macro to first find the "Total" in "C:C" then clear the Borders in that ROW and then place aborderon top and bottom of that ROW and if any cell in that ROW is not empty place a OUTLINEBorder.. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
color row using macro..
You are talking about that statement as part of this block of code, right?
For Each cc In Range(Cells(c.Row, 1), Cells(c.Row, lc)) If Len(Application.Trim(cc)) 0 Then cc.Borders.LineStyle = xlContinuous cc.Borders.Weight = xlMedium End If Next cc I would think the 'If Len(Application.Trim(cc)) 0 Then' statement should already be doing what you ask. Have you modified this block of code in any way? If yes, you need to show us what you did. Rick wrote in message ... Hi, Thanks. How can I make the statement below to only do the Borders to cells that are not empty, cells that have no values should not have borders. cc.Borders.LineStyle = xlContinuous Thanks. On Jul 6, 6:44 am, "Don Guillett" wrote: Just add another line below the DO ' Do Rows(c.row + 1).Insert -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... Hi, Something else came up, hope you can help! Can we add a line to say that when it finds the "Total" also insert a ROW below that Row Thanks On Jun 22, 3:26 pm, "Don Guillett" wrote: glad to help -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... Thanks, It is Great! On Jun 22, 6:48 am, "Don Guillett" wrote: I sent this SubColorTotalRow() Application.ScreenUpdating = False With Worksheets("sheet26") With Cells.SpecialCells(xlCellTypeLastCell) lc = .Column lr = .Row End With With .Rows("2:" & lr) .Font.Bold = False .Borders.LineStyle = xlNone .Interior.ColorIndex = xlNone End With With .Range("c2:c" & lr) Set c = .Find("Total", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do With Range(Cells(c.Row, 1), Cells(c.Row, lc)) .Font.Bold = True .Interior.ColorIndex = 6 .BorderAround , Weight:=xlMedium End With For Each cc In Range(Cells(c.Row, 1), Cells(c.Row, lc)) If Len(Application.Trim(cc)) 0 Then cc.Borders.LineStyle = xlContinuous cc.Borders.Weight = xlMedium End If Next cc Set c = .FindNext(c) Loop While Not c Is Nothing _ And c.Address < firstAddress End If End With End With Application.ScreenUpdating = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... What you want can be done. Let's see now we have an empty cell to the right we have a full cell to the right we have an empty cell so now all cells have a right and leftborderalso. Tell us how to NOT have aborderon the empty cell in the middle. You did not mention your layout or how many cells per row may be involved. If desired, send me a workbook to my address below and I'll have a look. -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... Hi, Thanks, Now I got the Top Post! How about if any cell in that ROW that we colored that is not empty place a OUTLINEBorder, not only the top and bottom but the sides too for those cells that are not empty. Thanks. On Jun 21, 11:38 am, "Don Guillett" wrote: Did you NOT see my last post with the change and the request to TOP POST -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... On Jun 21, 10:19 am, wrote: On Jun 21, 6:45 am, "Don Guillett" wrote: try this. change sheet25 to suit the name of your sheet and 500 to suit your last row Subcolortotalrow() With Worksheets("sheet25").Range("c1:c500") Set c = .Find("Total", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Rows(c.Row).Interior.ColorIndex = 6 Set c = .FindNext(c) Loop While Not c Is Nothing _ And c.Address < firstAddress End If End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... Hi, Is there any way to use a macro to find a word example "Total" in a column example "C:C" and when it finds the word it will color that row say "Blue" and then find next and if find more do the same all the way to the end of the column. Hi, Thanks it works great. Can I make another macro or add to this one, I need the macro to first find the "Total" in "C:C" then clear the Borders in that ROW and then place aborderon top and bottom of that ROW and if any cell in that ROW is not empty place a OUTLINEBorder. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
color row using macro..
Hi, Yes that is the Code, and no I have not modified it.
I believe the code looks at the rows and if any cells in the row that has a value it does the Borders to that row, but I want it to do the Borders only to each cell that has a value. Thanks, On Jul 6, 10:59*am, "Rick Rothstein \(MVP - VB\)" wrote: You are talking about that statement as part of this block of code, right? * * For Each cc In Range(Cells(c.Row, 1), Cells(c.Row, lc)) * * *If Len(Application.Trim(cc)) 0 Then * * * cc.Borders.LineStyle = xlContinuous * * * cc.Borders.Weight = xlMedium * * *End If * * Next cc I would think the 'If Len(Application.Trim(cc)) 0 Then' statement should already be doing what you ask. Have you modified this block of code in any way? If yes, you need to show us what you did. Rick wrote in message ... Hi, Thanks. How can I make the statement below to only do the Borders to cells that are not empty, cells that have no values should not have borders. cc.Borders.LineStyle = xlContinuous Thanks. On Jul 6, 6:44 am, "Don Guillett" wrote: Just add another line below the DO ' Do Rows(c.row + 1).Insert -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message .... Hi, Something else came up, hope you can help! Can we add a line to say that when it finds the "Total" also insert a ROW below that Row Thanks On Jun 22, 3:26 pm, "Don Guillett" wrote: glad to help -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message .... Thanks, It is Great! On Jun 22, 6:48 am, "Don Guillett" wrote: I sent this SubColorTotalRow() Application.ScreenUpdating = False With Worksheets("sheet26") With Cells.SpecialCells(xlCellTypeLastCell) lc = .Column lr = .Row End With With .Rows("2:" & lr) .Font.Bold = False .Borders.LineStyle = xlNone .Interior.ColorIndex = xlNone End With With .Range("c2:c" & lr) Set c = .Find("Total", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do With Range(Cells(c.Row, 1), Cells(c.Row, lc)) .Font.Bold = True .Interior.ColorIndex = 6 .BorderAround , Weight:=xlMedium End With For Each cc In Range(Cells(c.Row, 1), Cells(c.Row, lc)) If Len(Application.Trim(cc)) 0 Then cc.Borders.LineStyle = xlContinuous cc.Borders.Weight = xlMedium End If Next cc Set c = .FindNext(c) Loop While Not c Is Nothing _ And c.Address < firstAddress End If End With End With Application.ScreenUpdating = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... What you want can be done. Let's see now we have an empty cell to the right we have a full cell to the right we have an empty cell so now all cells have a right and leftborderalso. Tell us how to NOT have aborderon the empty cell in the middle. You did not mention your layout or how many cells per row may be involved. If desired, send me a workbook to my address below and I'll have a look. -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... Hi, Thanks, Now I got the Top Post! How about if any cell in that ROW that we colored that is not empty place a OUTLINEBorder, not only the top and bottom but the sides too for those cells that are not empty. Thanks. On Jun 21, 11:38 am, "Don Guillett" wrote: Did you NOT see my last post with the change and the request to TOP POST -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... On Jun 21, 10:19 am, wrote: On Jun 21, 6:45 am, "Don Guillett" wrote: try this. change sheet25 to suit the name of your sheet and 500 to suit your last row Subcolortotalrow() With Worksheets("sheet25").Range("c1:c500") Set c = .Find("Total", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Rows(c.Row).Interior.ColorIndex = 6 Set c = .FindNext(c) Loop While Not c Is Nothing _ And c.Address < firstAddress End If End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... Hi, Is there any way to use a macro to find a word example "Total" in a column example "C:C" and when it finds the word it will color that row say "Blue" and then find next and if find more do the same all the way to the end of the column. Hi, Thanks it works great. Can I make another macro or add to this one, I need the macro to first find the "Total" in "C:C" then clear the Borders in that ROW and then place aborderon top and bottom of that ROW and if any cell in that ROW is not empty place a OUTLINEBorder. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
color row using macro..
Sub Macro1() For x = 3 To 1500 For y = 3 To 1500 If Cells(x, 2).Value = Cells(y, 6).Value Then Cells(x, 7).Value = "true" Cells(x, 3).Interior.ColorIndex = 36 Cells(x, 8).Value = y Cells(y, 4).Interior.ColorIndex = 36 Else End If Next y Next x End Sub *** Sent via Developersdex http://www.developersdex.com *** |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Color macro | Excel Programming | |||
| Make text color match cell color with macro? | Excel Discussion (Misc queries) | |||
| Macro - color tab | Excel Discussion (Misc queries) | |||
| Color Row Macro Problem, adapted from Patrick Malloy macro | Excel Programming | |||
| macro color change | Excel Worksheet Functions | |||
Linear Mode
