+ Reply to Thread
Results 1 to 7 of 7

Apply Border

  1. #1
    Bill
    Guest

    Apply Border

    I cannot use conditional formatting because each of the three blocks already
    has formulas in them. Plus I need to accomplish to actions with these code.

    So I would like some assistance in developing the code to utilize the
    Worksheet_Change event using VBA.

    1. I am trying to apply a thick border to the left side of the column
    running from row 5 through row 56 for any column when the value in row 3 of
    that column equals 1.

    Thanks Bill

    2. The code would need to check row 3 starting with column C going right.
    Example check C3, D3 E3…. When the code hits the last cell that does not
    contain a value then I need to apply a thick border to the right side of the
    column running from row 5 through row 56 for the ending row


  2. #2
    Rowan
    Guest

    RE: Apply Border

    This should get you started. If not you will need to make your explanation a
    little clearer. E.G. do you want the last used column to have a right border
    or is it the first empty column that has the border.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim endCol As Integer
    If Target.Row = 3 And Target.Column > 2 And _
    Target.Value = 1 Then
    With Range(Cells(5, Target.Column), Cells(56, Target.Column))
    .Borders(xlEdgeLeft).Weight = xlThick
    End With
    endCol = Cells(3, Columns.Count).End(xlToLeft).Column
    With Range(Cells(5, endCol), Cells(56, endCol))
    .Borders(xlEdgeRight).Weight = xlThick
    End With
    End If
    End Sub

    Regards
    Rowan

    "Bill" wrote:

    > I cannot use conditional formatting because each of the three blocks already
    > has formulas in them. Plus I need to accomplish to actions with these code.
    >
    > So I would like some assistance in developing the code to utilize the
    > Worksheet_Change event using VBA.
    >
    > 1. I am trying to apply a thick border to the left side of the column
    > running from row 5 through row 56 for any column when the value in row 3 of
    > that column equals 1.
    >
    > Thanks Bill
    >
    > 2. The code would need to check row 3 starting with column C going right.
    > Example check C3, D3 E3…. When the code hits the last cell that does not
    > contain a value then I need to apply a thick border to the right side of the
    > column running from row 5 through row 56 for the ending row
    >


  3. #3
    Bill
    Guest

    RE: Apply Border

    I receive a type error with the code. If I put in in a blank worksheet with
    no other code it tends to work with one small exception. The range of cells
    in row three is updated automatically when the target address "a4" is
    updated. Can this be fixed.

    'from my sheet
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$4" Then
    Call DateFiller
    Call WeekendFiller
    Call BottomDataFiller
    End If

    Dim endCol As Integer
    If Target.Row = 3 And Target.Column > 2 And Target.Value = 1 Then
    With Range(Cells(5, Target.Column), Cells(56, Target.Column))
    .Borders(xlEdgeLeft).Weight = xlThick
    End With
    endCol = Cells(3, Columns.Count).End(xlToLeft).Column
    With Range(Cells(5, endCol), Cells(56, endCol))
    .Borders(xlEdgeRight).Weight = xlThick
    End With
    End If
    End Sub




    "Rowan" wrote:

    > This should get you started. If not you will need to make your explanation a
    > little clearer. E.G. do you want the last used column to have a right border
    > or is it the first empty column that has the border.
    >
    > Private Sub Worksheet_Change(ByVal Target As Range)
    > Dim endCol As Integer
    > If Target.Row = 3 And Target.Column > 2 And _
    > Target.Value = 1 Then
    > With Range(Cells(5, Target.Column), Cells(56, Target.Column))
    > .Borders(xlEdgeLeft).Weight = xlThick
    > End With
    > endCol = Cells(3, Columns.Count).End(xlToLeft).Column
    > With Range(Cells(5, endCol), Cells(56, endCol))
    > .Borders(xlEdgeRight).Weight = xlThick
    > End With
    > End If
    > End Sub
    >
    > Regards
    > Rowan
    >
    > "Bill" wrote:
    >
    > > I cannot use conditional formatting because each of the three blocks already
    > > has formulas in them. Plus I need to accomplish to actions with these code.
    > >
    > > So I would like some assistance in developing the code to utilize the
    > > Worksheet_Change event using VBA.
    > >
    > > 1. I am trying to apply a thick border to the left side of the column
    > > running from row 5 through row 56 for any column when the value in row 3 of
    > > that column equals 1.
    > >
    > > Thanks Bill
    > >
    > > 2. The code would need to check row 3 starting with column C going right.
    > > Example check C3, D3 E3…. When the code hits the last cell that does not
    > > contain a value then I need to apply a thick border to the right side of the
    > > column running from row 5 through row 56 for the ending row
    > >


  4. #4
    Rowan
    Guest

    RE: Apply Border

    Hi Bill

    I'm not sure where you are getting the error but if you want the borders
    applied after A4 is updated then try it something like this:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim endCol As Integer
    Dim Counter As Integer
    Application.EnableEvents = False
    If Target.Address = "$A$4" Then
    Call DateFiller
    Call WeekendFiller
    Call BottomDataFiller

    endCol = Cells(3, Columns.Count).End(xlToLeft).Column
    For Counter = 3 To endCol
    If Cells(3, Counter).Value = 1 Then
    With Range(Cells(5, Counter), Cells(56, Counter))
    .Borders(xlEdgeLeft).Weight = xlThick
    End With
    End If
    Next Counter
    With Range(Cells(5, endCol), Cells(56, endCol))
    .Borders(xlEdgeRight).Weight = xlThick
    End With
    End If
    Application.EnableEvents = True
    End Sub

    Hope this helps
    Rowan


    "Bill" wrote:

    > I receive a type error with the code. If I put in in a blank worksheet with
    > no other code it tends to work with one small exception. The range of cells
    > in row three is updated automatically when the target address "a4" is
    > updated. Can this be fixed.
    >
    > 'from my sheet
    > Private Sub Worksheet_Change(ByVal Target As Range)
    > If Target.Address = "$A$4" Then
    > Call DateFiller
    > Call WeekendFiller
    > Call BottomDataFiller
    > End If
    >
    > Dim endCol As Integer
    > If Target.Row = 3 And Target.Column > 2 And Target.Value = 1 Then
    > With Range(Cells(5, Target.Column), Cells(56, Target.Column))
    > .Borders(xlEdgeLeft).Weight = xlThick
    > End With
    > endCol = Cells(3, Columns.Count).End(xlToLeft).Column
    > With Range(Cells(5, endCol), Cells(56, endCol))
    > .Borders(xlEdgeRight).Weight = xlThick
    > End With
    > End If
    > End Sub
    >
    >
    >
    >
    > "Rowan" wrote:
    >
    > > This should get you started. If not you will need to make your explanation a
    > > little clearer. E.G. do you want the last used column to have a right border
    > > or is it the first empty column that has the border.
    > >
    > > Private Sub Worksheet_Change(ByVal Target As Range)
    > > Dim endCol As Integer
    > > If Target.Row = 3 And Target.Column > 2 And _
    > > Target.Value = 1 Then
    > > With Range(Cells(5, Target.Column), Cells(56, Target.Column))
    > > .Borders(xlEdgeLeft).Weight = xlThick
    > > End With
    > > endCol = Cells(3, Columns.Count).End(xlToLeft).Column
    > > With Range(Cells(5, endCol), Cells(56, endCol))
    > > .Borders(xlEdgeRight).Weight = xlThick
    > > End With
    > > End If
    > > End Sub
    > >
    > > Regards
    > > Rowan
    > >
    > > "Bill" wrote:
    > >
    > > > I cannot use conditional formatting because each of the three blocks already
    > > > has formulas in them. Plus I need to accomplish to actions with these code.
    > > >
    > > > So I would like some assistance in developing the code to utilize the
    > > > Worksheet_Change event using VBA.
    > > >
    > > > 1. I am trying to apply a thick border to the left side of the column
    > > > running from row 5 through row 56 for any column when the value in row 3 of
    > > > that column equals 1.
    > > >
    > > > Thanks Bill
    > > >
    > > > 2. The code would need to check row 3 starting with column C going right.
    > > > Example check C3, D3 E3…. When the code hits the last cell that does not
    > > > contain a value then I need to apply a thick border to the right side of the
    > > > column running from row 5 through row 56 for the ending row
    > > >


  5. #5
    Bill
    Guest

    RE: Apply Border

    Rowan

    Thanks. That worked perfect. Can you help me with one last thing? How can
    I make I guess an array so that the below code will apply the border to rows
    4, 10, 13, 19, 22, 24, 37.

    With Range(Cells(58, 2), Cells(58, endCol))
    ..Borders(xlEdgeBottom).Weight = xlThick
    End With

    Thanks
    Bill

    "Rowan" wrote:

    > Hi Bill
    >
    > I'm not sure where you are getting the error but if you want the borders
    > applied after A4 is updated then try it something like this:
    >
    > Private Sub Worksheet_Change(ByVal Target As Range)
    > Dim endCol As Integer
    > Dim Counter As Integer
    > Application.EnableEvents = False
    > If Target.Address = "$A$4" Then
    > Call DateFiller
    > Call WeekendFiller
    > Call BottomDataFiller
    >
    > endCol = Cells(3, Columns.Count).End(xlToLeft).Column
    > For Counter = 3 To endCol
    > If Cells(3, Counter).Value = 1 Then
    > With Range(Cells(5, Counter), Cells(56, Counter))
    > .Borders(xlEdgeLeft).Weight = xlThick
    > End With
    > End If
    > Next Counter
    > With Range(Cells(5, endCol), Cells(56, endCol))
    > .Borders(xlEdgeRight).Weight = xlThick
    > End With
    > End If
    > Application.EnableEvents = True
    > End Sub
    >
    > Hope this helps
    > Rowan
    >
    >
    > "Bill" wrote:
    >
    > > I receive a type error with the code. If I put in in a blank worksheet with
    > > no other code it tends to work with one small exception. The range of cells
    > > in row three is updated automatically when the target address "a4" is
    > > updated. Can this be fixed.
    > >
    > > 'from my sheet
    > > Private Sub Worksheet_Change(ByVal Target As Range)
    > > If Target.Address = "$A$4" Then
    > > Call DateFiller
    > > Call WeekendFiller
    > > Call BottomDataFiller
    > > End If
    > >
    > > Dim endCol As Integer
    > > If Target.Row = 3 And Target.Column > 2 And Target.Value = 1 Then
    > > With Range(Cells(5, Target.Column), Cells(56, Target.Column))
    > > .Borders(xlEdgeLeft).Weight = xlThick
    > > End With
    > > endCol = Cells(3, Columns.Count).End(xlToLeft).Column
    > > With Range(Cells(5, endCol), Cells(56, endCol))
    > > .Borders(xlEdgeRight).Weight = xlThick
    > > End With
    > > End If
    > > End Sub
    > >
    > >
    > >
    > >
    > > "Rowan" wrote:
    > >
    > > > This should get you started. If not you will need to make your explanation a
    > > > little clearer. E.G. do you want the last used column to have a right border
    > > > or is it the first empty column that has the border.
    > > >
    > > > Private Sub Worksheet_Change(ByVal Target As Range)
    > > > Dim endCol As Integer
    > > > If Target.Row = 3 And Target.Column > 2 And _
    > > > Target.Value = 1 Then
    > > > With Range(Cells(5, Target.Column), Cells(56, Target.Column))
    > > > .Borders(xlEdgeLeft).Weight = xlThick
    > > > End With
    > > > endCol = Cells(3, Columns.Count).End(xlToLeft).Column
    > > > With Range(Cells(5, endCol), Cells(56, endCol))
    > > > .Borders(xlEdgeRight).Weight = xlThick
    > > > End With
    > > > End If
    > > > End Sub
    > > >
    > > > Regards
    > > > Rowan
    > > >
    > > > "Bill" wrote:
    > > >
    > > > > I cannot use conditional formatting because each of the three blocks already
    > > > > has formulas in them. Plus I need to accomplish to actions with these code.
    > > > >
    > > > > So I would like some assistance in developing the code to utilize the
    > > > > Worksheet_Change event using VBA.
    > > > >
    > > > > 1. I am trying to apply a thick border to the left side of the column
    > > > > running from row 5 through row 56 for any column when the value in row 3 of
    > > > > that column equals 1.
    > > > >
    > > > > Thanks Bill
    > > > >
    > > > > 2. The code would need to check row 3 starting with column C going right.
    > > > > Example check C3, D3 E3…. When the code hits the last cell that does not
    > > > > contain a value then I need to apply a thick border to the right side of the
    > > > > column running from row 5 through row 56 for the ending row
    > > > >


  6. #6
    Bill
    Guest

    RE: Apply Border

    Rowan

    I apologize. Please disregard my previous email and if you have time and
    its possible help my change the code to this.

    Thanks. That worked perfect. Can you help me with one last thing? How can
    I make the below code so that it will apply the border to the top row of any
    cell within the Range("A5:A55) that contains text. The border would run from
    column 2 to the last column endcol.

    endCol = Cells(3, Columns.Count).End(xlToLeft).Column
    With Range(Cells(58, 2), Cells(58, endCol))
    ..Borders(xlEdgeTop).Weight = xlThick
    End With


    "Bill" wrote:

    > Rowan
    >
    > Thanks. That worked perfect. Can you help me with one last thing? How can
    > I make I guess an array so that the below code will apply the border to rows
    > 4, 10, 13, 19, 22, 24, 37.
    >
    > With Range(Cells(58, 2), Cells(58, endCol))
    > .Borders(xlEdgeBottom).Weight = xlThick
    > End With
    >
    > Thanks
    > Bill
    >
    > "Rowan" wrote:
    >
    > > Hi Bill
    > >
    > > I'm not sure where you are getting the error but if you want the borders
    > > applied after A4 is updated then try it something like this:
    > >
    > > Private Sub Worksheet_Change(ByVal Target As Range)
    > > Dim endCol As Integer
    > > Dim Counter As Integer
    > > Application.EnableEvents = False
    > > If Target.Address = "$A$4" Then
    > > Call DateFiller
    > > Call WeekendFiller
    > > Call BottomDataFiller
    > >
    > > endCol = Cells(3, Columns.Count).End(xlToLeft).Column
    > > For Counter = 3 To endCol
    > > If Cells(3, Counter).Value = 1 Then
    > > With Range(Cells(5, Counter), Cells(56, Counter))
    > > .Borders(xlEdgeLeft).Weight = xlThick
    > > End With
    > > End If
    > > Next Counter
    > > With Range(Cells(5, endCol), Cells(56, endCol))
    > > .Borders(xlEdgeRight).Weight = xlThick
    > > End With
    > > End If
    > > Application.EnableEvents = True
    > > End Sub
    > >
    > > Hope this helps
    > > Rowan
    > >
    > >
    > > "Bill" wrote:
    > >
    > > > I receive a type error with the code. If I put in in a blank worksheet with
    > > > no other code it tends to work with one small exception. The range of cells
    > > > in row three is updated automatically when the target address "a4" is
    > > > updated. Can this be fixed.
    > > >
    > > > 'from my sheet
    > > > Private Sub Worksheet_Change(ByVal Target As Range)
    > > > If Target.Address = "$A$4" Then
    > > > Call DateFiller
    > > > Call WeekendFiller
    > > > Call BottomDataFiller
    > > > End If
    > > >
    > > > Dim endCol As Integer
    > > > If Target.Row = 3 And Target.Column > 2 And Target.Value = 1 Then
    > > > With Range(Cells(5, Target.Column), Cells(56, Target.Column))
    > > > .Borders(xlEdgeLeft).Weight = xlThick
    > > > End With
    > > > endCol = Cells(3, Columns.Count).End(xlToLeft).Column
    > > > With Range(Cells(5, endCol), Cells(56, endCol))
    > > > .Borders(xlEdgeRight).Weight = xlThick
    > > > End With
    > > > End If
    > > > End Sub
    > > >
    > > >
    > > >
    > > >
    > > > "Rowan" wrote:
    > > >
    > > > > This should get you started. If not you will need to make your explanation a
    > > > > little clearer. E.G. do you want the last used column to have a right border
    > > > > or is it the first empty column that has the border.
    > > > >
    > > > > Private Sub Worksheet_Change(ByVal Target As Range)
    > > > > Dim endCol As Integer
    > > > > If Target.Row = 3 And Target.Column > 2 And _
    > > > > Target.Value = 1 Then
    > > > > With Range(Cells(5, Target.Column), Cells(56, Target.Column))
    > > > > .Borders(xlEdgeLeft).Weight = xlThick
    > > > > End With
    > > > > endCol = Cells(3, Columns.Count).End(xlToLeft).Column
    > > > > With Range(Cells(5, endCol), Cells(56, endCol))
    > > > > .Borders(xlEdgeRight).Weight = xlThick
    > > > > End With
    > > > > End If
    > > > > End Sub
    > > > >
    > > > > Regards
    > > > > Rowan
    > > > >
    > > > > "Bill" wrote:
    > > > >
    > > > > > I cannot use conditional formatting because each of the three blocks already
    > > > > > has formulas in them. Plus I need to accomplish to actions with these code.
    > > > > >
    > > > > > So I would like some assistance in developing the code to utilize the
    > > > > > Worksheet_Change event using VBA.
    > > > > >
    > > > > > 1. I am trying to apply a thick border to the left side of the column
    > > > > > running from row 5 through row 56 for any column when the value in row 3 of
    > > > > > that column equals 1.
    > > > > >
    > > > > > Thanks Bill
    > > > > >
    > > > > > 2. The code would need to check row 3 starting with column C going right.
    > > > > > Example check C3, D3 E3…. When the code hits the last cell that does not
    > > > > > contain a value then I need to apply a thick border to the right side of the
    > > > > > column running from row 5 through row 56 for the ending row
    > > > > >


  7. #7
    Rowan
    Guest

    RE: Apply Border

    Hi Bill

    Maybe like this - untested

    dim Cell as Range
    dim endCol as integer
    endCol = Cells(3, Columns.Count).End(xlToLeft).Column
    for each cell in range("A5:A55")
    if cell.value <> empty then
    With Range(Cells(cell.row, 2), Cells(cell.row, endCol))
    ..Borders(xlEdgeTop).Weight = xlThick
    End With
    end if
    next cell

    Regards
    Rowan

    "Bill" wrote:

    > Rowan
    >
    > I apologize. Please disregard my previous email and if you have time and
    > its possible help my change the code to this.
    >
    > Thanks. That worked perfect. Can you help me with one last thing? How can
    > I make the below code so that it will apply the border to the top row of any
    > cell within the Range("A5:A55) that contains text. The border would run from
    > column 2 to the last column endcol.
    >
    > endCol = Cells(3, Columns.Count).End(xlToLeft).Column
    > With Range(Cells(58, 2), Cells(58, endCol))
    > .Borders(xlEdgeTop).Weight = xlThick
    > End With
    >
    >
    > "Bill" wrote:
    >
    > > Rowan
    > >
    > > Thanks. That worked perfect. Can you help me with one last thing? How can
    > > I make I guess an array so that the below code will apply the border to rows
    > > 4, 10, 13, 19, 22, 24, 37.
    > >
    > > With Range(Cells(58, 2), Cells(58, endCol))
    > > .Borders(xlEdgeBottom).Weight = xlThick
    > > End With
    > >
    > > Thanks
    > > Bill
    > >
    > > "Rowan" wrote:
    > >
    > > > Hi Bill
    > > >
    > > > I'm not sure where you are getting the error but if you want the borders
    > > > applied after A4 is updated then try it something like this:
    > > >
    > > > Private Sub Worksheet_Change(ByVal Target As Range)
    > > > Dim endCol As Integer
    > > > Dim Counter As Integer
    > > > Application.EnableEvents = False
    > > > If Target.Address = "$A$4" Then
    > > > Call DateFiller
    > > > Call WeekendFiller
    > > > Call BottomDataFiller
    > > >
    > > > endCol = Cells(3, Columns.Count).End(xlToLeft).Column
    > > > For Counter = 3 To endCol
    > > > If Cells(3, Counter).Value = 1 Then
    > > > With Range(Cells(5, Counter), Cells(56, Counter))
    > > > .Borders(xlEdgeLeft).Weight = xlThick
    > > > End With
    > > > End If
    > > > Next Counter
    > > > With Range(Cells(5, endCol), Cells(56, endCol))
    > > > .Borders(xlEdgeRight).Weight = xlThick
    > > > End With
    > > > End If
    > > > Application.EnableEvents = True
    > > > End Sub
    > > >
    > > > Hope this helps
    > > > Rowan
    > > >
    > > >
    > > > "Bill" wrote:
    > > >
    > > > > I receive a type error with the code. If I put in in a blank worksheet with
    > > > > no other code it tends to work with one small exception. The range of cells
    > > > > in row three is updated automatically when the target address "a4" is
    > > > > updated. Can this be fixed.
    > > > >
    > > > > 'from my sheet
    > > > > Private Sub Worksheet_Change(ByVal Target As Range)
    > > > > If Target.Address = "$A$4" Then
    > > > > Call DateFiller
    > > > > Call WeekendFiller
    > > > > Call BottomDataFiller
    > > > > End If
    > > > >
    > > > > Dim endCol As Integer
    > > > > If Target.Row = 3 And Target.Column > 2 And Target.Value = 1 Then
    > > > > With Range(Cells(5, Target.Column), Cells(56, Target.Column))
    > > > > .Borders(xlEdgeLeft).Weight = xlThick
    > > > > End With
    > > > > endCol = Cells(3, Columns.Count).End(xlToLeft).Column
    > > > > With Range(Cells(5, endCol), Cells(56, endCol))
    > > > > .Borders(xlEdgeRight).Weight = xlThick
    > > > > End With
    > > > > End If
    > > > > End Sub
    > > > >
    > > > >
    > > > >
    > > > >
    > > > > "Rowan" wrote:
    > > > >
    > > > > > This should get you started. If not you will need to make your explanation a
    > > > > > little clearer. E.G. do you want the last used column to have a right border
    > > > > > or is it the first empty column that has the border.
    > > > > >
    > > > > > Private Sub Worksheet_Change(ByVal Target As Range)
    > > > > > Dim endCol As Integer
    > > > > > If Target.Row = 3 And Target.Column > 2 And _
    > > > > > Target.Value = 1 Then
    > > > > > With Range(Cells(5, Target.Column), Cells(56, Target.Column))
    > > > > > .Borders(xlEdgeLeft).Weight = xlThick
    > > > > > End With
    > > > > > endCol = Cells(3, Columns.Count).End(xlToLeft).Column
    > > > > > With Range(Cells(5, endCol), Cells(56, endCol))
    > > > > > .Borders(xlEdgeRight).Weight = xlThick
    > > > > > End With
    > > > > > End If
    > > > > > End Sub
    > > > > >
    > > > > > Regards
    > > > > > Rowan
    > > > > >
    > > > > > "Bill" wrote:
    > > > > >
    > > > > > > I cannot use conditional formatting because each of the three blocks already
    > > > > > > has formulas in them. Plus I need to accomplish to actions with these code.
    > > > > > >
    > > > > > > So I would like some assistance in developing the code to utilize the
    > > > > > > Worksheet_Change event using VBA.
    > > > > > >
    > > > > > > 1. I am trying to apply a thick border to the left side of the column
    > > > > > > running from row 5 through row 56 for any column when the value in row 3 of
    > > > > > > that column equals 1.
    > > > > > >
    > > > > > > Thanks Bill
    > > > > > >
    > > > > > > 2. The code would need to check row 3 starting with column C going right.
    > > > > > > Example check C3, D3 E3…. When the code hits the last cell that does not
    > > > > > > contain a value then I need to apply a thick border to the right side of the
    > > > > > > column running from row 5 through row 56 for the ending row
    > > > > > >


+ 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