Help with border macro

[malik641]

I have this code where if any value is entered into the Column A then it would border that row from columns A through H. And if the cell in column A was deleted, the border is deleted.

I want to make it so that it will border from Column A to the last Column in row 1 that is <>&quot;&quot;

Here's the code:

Private Sub Worksheet_Change(ByVal Target As Range)
'Borders the previous row if a value is entered in the A:A column
If Target.Column = 1 Then
If Target.Value = Empty Then
Range(Target.Offset(0, 0), Target.Offset(0, 7)) _
.Borders.LineStyle = xlNone
Else
Range(Target.Offset(0, 0), Target.Offset(0, 7)) _
.Borders.Weight = xlThin
End If
End If
End Sub

[Norie]

joseph

The condition for the last column didn't come through correctly in the post.

Do you want to apply the border in every column with data?


Private Sub Worksheet_Change(ByVal Target As Range)
'Borders the previous row if a value is entered in the A:A column
Dim LastCol As Long
If Target.Column = 1 Then
LastCol = Range("IV" & Target.Row).End(xlToLeft).Column - 1
LastCol = IIf(LastCol < 0, 0, LastCol)
If Target.Value = Empty Then
Range(Target.Offset(0, 0), Target.Offset(0, LastCol)) _
.Borders.LineStyle = xlNone
Else
Range(Target.Offset(0, 0), Target.Offset(0, LastCol)) _
.Borders.Weight = xlThin
End If
End If
End Sub

[malik641]

joseph

The condition for the last column didn't come through correctly in the post.

Do you want to apply the border in every column with data?


Private Sub Worksheet_Change(ByVal Target As Range)
'Borders the previous row if a value is entered in the A:A column
Dim LastCol As Long
If Target.Column = 1 Then
LastCol = Range("IV" & Target.Row).End(xlToLeft).Column - 1
LastCol = IIf(LastCol < 0, 0, LastCol)
If Target.Value = Empty Then
Range(Target.Offset(0, 0), Target.Offset(0, LastCol)) _
.Borders.LineStyle = xlNone
Else
Range(Target.Offset(0, 0), Target.Offset(0, LastCol)) _
.Borders.Weight = xlThin
End If
End If
End Sub

Norie,
Beautiful! Thanks so much, that's exactly what I wanted. I only had to change one thing:

From:

LastCol = Range("IV" & Target.Row).End(xlToLeft).Column - 1
To:

LastCol = Range("IV" & 1).End(xlToLeft).Column - 1

Works awesome now!! :thumb:thumb
Thanks again!

[Norie]

I thought you might have varying no of columns containing data in the rows, that's why I used Target.Row.

[malik641]

Right.
It probably will, but I'd rather the border to be consistent for reports and so the user knows that there shouldn't be anything past the point of the last column in row 1.
Again, thanks!

[malik641]

Okay, I know I said solved....but I need one more thing.
When I delete/insert a row, I get an error....how do I avoid this error when deleting/inserting rows???

This is where I get the error:


If Target.Value = Empty Then

[malik641]

Nevermind...I changed the macro so that when a value is entered in column B, to border the columns from A to end columns in row 1. Here it is (SLIGHTLY modified)


Private Sub Worksheet_Change(ByVal Target As Range)
'Borders the previous row if a value is entered in the B:B column
Dim LastCol As Long
If Target.Column = 2 Then
LastCol = Range("IV" & 1).End(xlToLeft).Column - 1
LastCol = IIf(LastCol < 0, 0, LastCol)
If Target.Value = Empty Then
Range(Target.Offset(0, -1), Target.Offset(0, LastCol - 1)) _
.Borders.LineStyle = xlNone
Else
Range(Target.Offset(0, -1), Target.Offset(0, LastCol - 1)) _
.Borders.Weight = xlThin
End If
End If
End Sub


Works good now!