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

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> >
> > 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
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > >
> > > 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
> >
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >
> > > > 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
> > >
> > >
> > >
>
>
>