Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a line in a row
Hello,
I would like to add a border using vba coding to a worksheet this should only applies when there is a cell containing the text "Total" in column A. Now I want the border on top of the "Total" starting from column A - H. The worksheet is large and will have many "Total" in it. How do I code that? Any tips will be appreciated. Thank you in advance. Regards, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a line in a row
Try this macro...
Sub BorderOnTotal() Dim R As Range Dim FirstAddress As String With Worksheets("Sheet3").Columns(1) Set R = .Find("Total") If Not R Is Nothing Then FirstAddress = R.Address Do R.Borders(xlEdgeTop).LineStyle = xlContinuous Set R = .FindNext(R) Loop While Not R Is Nothing And R.Address < FirstAddress End If End With End Sub -- Rick (MVP - Excel) "CAM" wrote in message ... Hello, I would like to add a border using vba coding to a worksheet this should only applies when there is a cell containing the text "Total" in column A. Now I want the border on top of the "Total" starting from column A - H. The worksheet is large and will have many "Total" in it. How do I code that? Any tips will be appreciated. Thank you in advance. Regards, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a line in a row
Whoops! I forgot the A to H part. Try this macro instead...
Sub BorderOnTotal() Dim R As Range Dim FirstAddress As String With Worksheets("Sheet3").Columns(1) Set R = .Find("Total") If Not R Is Nothing Then FirstAddress = R.Address Do R.Resize(1, 8).Borders(xlEdgeTop).LineStyle = xlContinuous Set R = .FindNext(R) Loop While Not R Is Nothing And R.Address < FirstAddress End If End With End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Try this macro... Sub BorderOnTotal() Dim R As Range Dim FirstAddress As String With Worksheets("Sheet3").Columns(1) Set R = .Find("Total") If Not R Is Nothing Then FirstAddress = R.Address Do R.Borders(xlEdgeTop).LineStyle = xlContinuous Set R = .FindNext(R) Loop While Not R Is Nothing And R.Address < FirstAddress End If End With End Sub -- Rick (MVP - Excel) "CAM" wrote in message ... Hello, I would like to add a border using vba coding to a worksheet this should only applies when there is a cell containing the text "Total" in column A. Now I want the border on top of the "Total" starting from column A - H. The worksheet is large and will have many "Total" in it. How do I code that? Any tips will be appreciated. Thank you in advance. Regards, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a line in a row
Try this
Sub underlineabovetotal() With Worksheets("sheet3").Range("a1:a500") Set c = .Find("Total", After:=Range("a1"), LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext,MatchCase:=False) If Not c Is Nothing Then firstAddress = c.Address Do With .cells(c.Row - 1, "a").Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThick End With 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 "CAM" wrote in message ... Hello, I would like to add a border using vba coding to a worksheet this should only applies when there is a cell containing the text "Total" in column A. Now I want the border on top of the "Total" starting from column A - H. The worksheet is large and will have many "Total" in it. How do I code that? Any tips will be appreciated. Thank you in advance. Regards, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a line in a row
Rick I changed from ("Sheet3") to ("Sheet1"). Thanks for you help. I
really appreciate your skills. Regards, "Rick Rothstein" wrote in message ... Whoops! I forgot the A to H part. Try this macro instead... Sub BorderOnTotal() Dim R As Range Dim FirstAddress As String With Worksheets("Sheet3").Columns(1) Set R = .Find("Total") If Not R Is Nothing Then FirstAddress = R.Address Do R.Resize(1, 8).Borders(xlEdgeTop).LineStyle = xlContinuous Set R = .FindNext(R) Loop While Not R Is Nothing And R.Address < FirstAddress End If End With End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Try this macro... Sub BorderOnTotal() Dim R As Range Dim FirstAddress As String With Worksheets("Sheet3").Columns(1) Set R = .Find("Total") If Not R Is Nothing Then FirstAddress = R.Address Do R.Borders(xlEdgeTop).LineStyle = xlContinuous Set R = .FindNext(R) Loop While Not R Is Nothing And R.Address < FirstAddress End If End With End Sub -- Rick (MVP - Excel) "CAM" wrote in message ... Hello, I would like to add a border using vba coding to a worksheet this should only applies when there is a cell containing the text "Total" in column A. Now I want the border on top of the "Total" starting from column A - H. The worksheet is large and will have many "Total" in it. How do I code that? Any tips will be appreciated. Thank you in advance. Regards, |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a line in a row
Thanks Don, I appreciate the tip.
Regards, "Don Guillett" wrote in message ... Try this Sub underlineabovetotal() With Worksheets("sheet3").Range("a1:a500") Set c = .Find("Total", After:=Range("a1"), LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext,MatchCase:=False) If Not c Is Nothing Then firstAddress = c.Address Do With .cells(c.Row - 1, "a").Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThick End With 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 "CAM" wrote in message ... Hello, I would like to add a border using vba coding to a worksheet this should only applies when there is a cell containing the text "Total" in column A. Now I want the border on top of the "Total" starting from column A - H. The worksheet is large and will have many "Total" in it. How do I code that? Any tips will be appreciated. Thank you in advance. Regards, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem adding vertical marker line to line chart | Charts and Charting in Excel | |||
Adding a Second and Third Line to an Existing Stacked Bar/Line Cha | Charts and Charting in Excel | |||
Adding a line to the line/column chart? | Charts and Charting in Excel | |||
Chart Type - Line Column adding a 2nd line | Charts and Charting in Excel | |||
Adding a new Line | Excel Programming |