Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply Border to range when first cell has text.
I need to apply a Top border starting from column A to the LastCol for any row where text is detected in that cell of column A. The range for the detection process is from A5:A56. Thanks LastCol = Cells(3, Columns.Count).End(xlToLeft).Column With Range(Cells(2, 1), Cells(2, LastCol + 5)) .Borders(xlEdgeTop).LineStyle = xlNone End With With Range(Cells(2, 1), Cells(2, LastCol)) .Borders(xlEdgeTop).Weight = xlThick End With As an example if cell A11 contained any text then a top border would be applied starting from A11 to the last column. The last column (LastCol) is an interger that changes depending a cell. Bill |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply Border to range when first cell has text.
Dim cell as Range
for each cell in Range("A5:A56") if cell.Text < "" then LastCol = Cells(cell.Row, Columns.Count).End(xlToLeft).Column With Range(Cells(cell.row, 1), Cells(cell.Row, LastCol + 5)) .Borders(xlEdgeTop).LineStyle = xlNone End With With Range(Cells(Cell.row, 1), Cells(cell.row, LastCol)) .Borders(xlEdgeTop).Weight = xlThick End With end If Next -- Regards, Tom Ogilvy "Bill" wrote in message ... I need to apply a Top border starting from column A to the LastCol for any row where text is detected in that cell of column A. The range for the detection process is from A5:A56. Thanks LastCol = Cells(3, Columns.Count).End(xlToLeft).Column With Range(Cells(2, 1), Cells(2, LastCol + 5)) .Borders(xlEdgeTop).LineStyle = xlNone End With With Range(Cells(2, 1), Cells(2, LastCol)) .Borders(xlEdgeTop).Weight = xlThick End With As an example if cell A11 contained any text then a top border would be applied starting from A11 to the last column. The last column (LastCol) is an interger that changes depending a cell. Bill |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply Border to range when first cell has text.
Tom
I have this under the worksheet_change event. When I enter text outside the range (ex. D5) the screen flickers for several seconds. Is there a way to use the target.address event to limit the execution/running of the code only to when data is enter the range of A5 through A56. Thanks Bill "Tom Ogilvy" wrote: Dim cell as Range for each cell in Range("A5:A56") if cell.Text < "" then LastCol = Cells(cell.Row, Columns.Count).End(xlToLeft).Column With Range(Cells(cell.row, 1), Cells(cell.Row, LastCol + 5)) .Borders(xlEdgeTop).LineStyle = xlNone End With With Range(Cells(Cell.row, 1), Cells(cell.row, LastCol)) .Borders(xlEdgeTop).Weight = xlThick End With end If Next -- Regards, Tom Ogilvy "Bill" wrote in message ... I need to apply a Top border starting from column A to the LastCol for any row where text is detected in that cell of column A. The range for the detection process is from A5:A56. Thanks LastCol = Cells(3, Columns.Count).End(xlToLeft).Column With Range(Cells(2, 1), Cells(2, LastCol + 5)) .Borders(xlEdgeTop).LineStyle = xlNone End With With Range(Cells(2, 1), Cells(2, LastCol)) .Borders(xlEdgeTop).Weight = xlThick End With As an example if cell A11 contained any text then a top border would be applied starting from A11 to the last column. The last column (LastCol) is an interger that changes depending a cell. Bill |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply Border to range when first cell has text.
Dim cell as Range If Target.count 1 then Exit sub If Intersect(Target, Range("A5:A56")) is nothing then exit sub set cell = Target if cell.Text < "" then LastCol = Cells(cell.Row, Columns.Count).End(xlToLeft).Column With Range(Cells(cell.row, 1), Cells(cell.Row, LastCol + 5)) .Borders(xlEdgeTop).LineStyle = xlNone End With With Range(Cells(Cell.row, 1), Cells(cell.row, LastCol)) .Borders(xlEdgeTop).Weight = xlThick End With end If -- Regards, Tom Ogilvy "Bill" wrote in message ... Tom I have this under the worksheet_change event. When I enter text outside the range (ex. D5) the screen flickers for several seconds. Is there a way to use the target.address event to limit the execution/running of the code only to when data is enter the range of A5 through A56. Thanks Bill "Tom Ogilvy" wrote: Dim cell as Range for each cell in Range("A5:A56") if cell.Text < "" then LastCol = Cells(cell.Row, Columns.Count).End(xlToLeft).Column With Range(Cells(cell.row, 1), Cells(cell.Row, LastCol + 5)) .Borders(xlEdgeTop).LineStyle = xlNone End With With Range(Cells(Cell.row, 1), Cells(cell.row, LastCol)) .Borders(xlEdgeTop).Weight = xlThick End With end If Next -- Regards, Tom Ogilvy "Bill" wrote in message ... I need to apply a Top border starting from column A to the LastCol for any row where text is detected in that cell of column A. The range for the detection process is from A5:A56. Thanks LastCol = Cells(3, Columns.Count).End(xlToLeft).Column With Range(Cells(2, 1), Cells(2, LastCol + 5)) .Borders(xlEdgeTop).LineStyle = xlNone End With With Range(Cells(2, 1), Cells(2, LastCol)) .Borders(xlEdgeTop).Weight = xlThick End With As an example if cell A11 contained any text then a top border would be applied starting from A11 to the last column. The last column (LastCol) is an interger that changes depending a cell. Bill |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply Border to range when first cell has text.
Tom
I have a spreadsheet that I am having difficulty achieving the desired effect. You solutions in the past have always been outstanding. Is this anyway I can send it to you and if you have the time take a look at it. I may be doing it the wrong way. Thanks either way for all your assistance Bill "Tom Ogilvy" wrote: Dim cell as Range If Target.count 1 then Exit sub If Intersect(Target, Range("A5:A56")) is nothing then exit sub set cell = Target if cell.Text < "" then LastCol = Cells(cell.Row, Columns.Count).End(xlToLeft).Column With Range(Cells(cell.row, 1), Cells(cell.Row, LastCol + 5)) .Borders(xlEdgeTop).LineStyle = xlNone End With With Range(Cells(Cell.row, 1), Cells(cell.row, LastCol)) .Borders(xlEdgeTop).Weight = xlThick End With end If -- Regards, Tom Ogilvy "Bill" wrote in message ... Tom I have this under the worksheet_change event. When I enter text outside the range (ex. D5) the screen flickers for several seconds. Is there a way to use the target.address event to limit the execution/running of the code only to when data is enter the range of A5 through A56. Thanks Bill "Tom Ogilvy" wrote: Dim cell as Range for each cell in Range("A5:A56") if cell.Text < "" then LastCol = Cells(cell.Row, Columns.Count).End(xlToLeft).Column With Range(Cells(cell.row, 1), Cells(cell.Row, LastCol + 5)) .Borders(xlEdgeTop).LineStyle = xlNone End With With Range(Cells(Cell.row, 1), Cells(cell.row, LastCol)) .Borders(xlEdgeTop).Weight = xlThick End With end If Next -- Regards, Tom Ogilvy "Bill" wrote in message ... I need to apply a Top border starting from column A to the LastCol for any row where text is detected in that cell of column A. The range for the detection process is from A5:A56. Thanks LastCol = Cells(3, Columns.Count).End(xlToLeft).Column With Range(Cells(2, 1), Cells(2, LastCol + 5)) .Borders(xlEdgeTop).LineStyle = xlNone End With With Range(Cells(2, 1), Cells(2, LastCol)) .Borders(xlEdgeTop).Weight = xlThick End With As an example if cell A11 contained any text then a top border would be applied starting from A11 to the last column. The last column (LastCol) is an interger that changes depending a cell. Bill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text overlapping cell border above it | Excel Discussion (Misc queries) | |||
How to apply a name to a cell range | Excel Discussion (Misc queries) | |||
Apply Hyperlink to Text in Cell | Excel Discussion (Misc queries) | |||
Apply Border | Excel Programming | |||
Apply Border based on last Day of Month | Excel Programming |