Border Row Macro problem

[malik641]

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]

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]

Awesome! That's that fastest "solved" I've ever seen!
Thanks Johnske!