AutoFill border

Hi all,

Goal: if a value is in Column B, have the inside border auto fill down, in Columns D through BE.
From Row 5 down to that row.

This works for the row where i put the value in Column B but if i clear out some of the bottom rows
in Column B the Format stays.

I guess what i am trying to do is clear the format on that row if the value
in Column B is tken out. Been trying some IF / ELSE stuff but to no avail.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LRow As Long
    
    If Target.Column = 2 Then
        LRow = Cells(Rows.Count, 5).End(xlUp).Row
        With Range("D" & Target.Row, "BE" & Target.Row)
            
            With .Borders(xlInsideVertical)
                .LineStyle = xlDash
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            
        End With
    End If
End Sub

Hi mudraker,

Thanks for your reply. As written your code clears all the Borders in Columns
D:BE as i asked, but my code dosen't fill them back in, down to the last cell in Column B.

So i will go back to the drawing board.

Seems this line isn't doing anything.

LRow = Cells(Rows.Count, 5).End(xlUp).Row

I am trying to make it start on line 5, going down.

Originally Posted by mudraker

Try clearing the borders for columns D to BE - all rows then have your macro apply border formating

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim LRow As Long
   
   If Target.Column = 2 Then
      With Columns("d:be")
         .Borders(xlDiagonalDown).LineStyle = xlNone
         .Borders(xlDiagonalUp).LineStyle = xlNone
         .Borders(xlEdgeLeft).LineStyle = xlNone
         .Borders(xlEdgeTop).LineStyle = xlNone
         .Borders(xlEdgeBottom).LineStyle = xlNone
         .Borders(xlEdgeRight).LineStyle = xlNone
         .Borders(xlInsideVertical).LineStyle = xlNone
         .Borders(xlInsideHorizontal).LineStyle = xlNone
      End With
      LRow = Cells(Rows.Count, 5).End(xlUp).Row
      With Range("D" & Target.Row, "BE" & Target.Row)
      
         With .Borders(xlInsideVertical)
            .LineStyle = xlDash
            .Weight = xlThin
            .ColorIndex = xlAutomatic
         End With
      
      End With
   End If
End Sub

Your code starts at the bottom row in column 5 (E) and goes up. But you do not use the variable result within your code.



Try changing your code the following 2 commands

LRow = Cells(Rows.Count, "b").End(xlUp).Row
or
LRow = Cells(5, "b").End(xlDown).Row

And

With Range("D" & LRow, "BE" & Lrow)

Ok here is where i am at:

It seems that it does everything i need except when i put a value
in Column B, the formating occurs only on that row and not from
row 5 down to that line. So all the rows in between are 'no borders'

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LRow As Long
    
    If Target.Column = 2 Then
        With Columns("D:BE")
            .Borders(xlInsideVertical).LineStyle = xlNone
        End With
        'old
        '        LRow = Cells(Rows.Count, 5).End(xlUp).Row
        'new
        '        LRow = Cells(Rows.Count, "B").End(xlUp).Row
        'new
        LRow = Cells(5, "B").End(xlDown).Row
        
        With Range("D" & LRow, "BE" & LRow)
            
            With .Borders(xlInsideVertical)
                .LineStyle = xlDash
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            
        End With
    End If
End Sub

Originally Posted by mudraker

Your code starts at the bottom row in column 5 (E) and goes up. But you do not use the variable result within your code.



Try changing your code the following 2 commands

LRow = Cells(Rows.Count, "b").End(xlUp).Row
or
LRow = Cells(5, "b").End(xlDown).Row

And

With Range("D" & LRow, "BE" & Lrow)