+ Reply to Thread
Results 1 to 5 of 5

Apply Border to range when first cell has text.

  1. #1
    Bill
    Guest

    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. #2
    Tom Ogilvy
    Guest

    Re: 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" <Bill@discussions.microsoft.com> wrote in message
    news:0A10ECA0-E411-4D26-A2D7-BD2DCC34989C@microsoft.com...
    >
    > 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. #3
    Bill
    Guest

    Re: 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" <Bill@discussions.microsoft.com> wrote in message
    > news:0A10ECA0-E411-4D26-A2D7-BD2DCC34989C@microsoft.com...
    > >
    > > 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. #4
    Tom Ogilvy
    Guest

    Re: 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" <Bill@discussions.microsoft.com> wrote in message
    news:2B395FC8-69C9-4430-8448-9734C782B66D@microsoft.com...
    > 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" <Bill@discussions.microsoft.com> wrote in message
    > > news:0A10ECA0-E411-4D26-A2D7-BD2DCC34989C@microsoft.com...
    > > >
    > > > 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. #5
    Bill
    Guest

    Re: 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" <Bill@discussions.microsoft.com> wrote in message
    > news:2B395FC8-69C9-4430-8448-9734C782B66D@microsoft.com...
    > > 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" <Bill@discussions.microsoft.com> wrote in message
    > > > news:0A10ECA0-E411-4D26-A2D7-BD2DCC34989C@microsoft.com...
    > > > >
    > > > > 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1