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

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

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

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

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