PDA

View Full Version : [SOLVED] Border Row Macro problem



malik641
08-09-2005, 05:14 AM
I have a macro that borders an entire row (up to the last column in 1:1 that has data) if any value is entered in column B. And it is set so that if a value is deleted from column B, that the border would be removed.

My problem is that if the user deletes two values (or more) at the same time (i.e. highlighted, then deleted) then I get an error with the code. Take a look:



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

The red text is where I get the error. I think the same would happen if the user tries to copy two values (or more) into column B.

Anybody have an idea how to avoid this???

johnske
08-09-2005, 05:30 AM
one simple addition fixes this prob...


Option Explicit

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)
On Error Resume Next
If Target = 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

malik641
08-09-2005, 05:36 AM
Awesome! That's that fastest "solved" I've ever seen!
Thanks Johnske!