Sleeper: Bordering rows help (again)

[malik641]

A while ago I received some code to border a row if a value was entered into Column B. Here it is:



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

The problem with this is that it doesn't border rows if a value in column B is copied down rows (click and drag, as well as copy and paste). How can I correct this?

[mark007]

Only a guess but it could be beacuse of multiple cells being affaceted. Try changing the code to:



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
Dim R as Range
LastCol = Range("IV" & 1).End(xlToLeft).Column - 1
LastCol = IIf(LastCol < 0, 0, LastCol)
On Error Resume Next
For Each R In Target
If R.Column = 2 Then
If R = Empty Then
Range(R.Offset(0, -1), R.Offset(0, LastCol - 1)) _
.Borders.LineStyle = xlNone
Else
Range(R.Offset(0, -1), R.Offset(0, LastCol - 1)) _
.Borders.Weight = xlThin
End If
End If
Next
End Sub


:)

[malik641]

Good guess. It worked! Awesome :beerchug:


Another question. I noticed that because of the worksheet_change event I'm unable to "Undo" ANYTHING I do in the column that is effected by the macro (e.g. Type a value into column B, or copy values into column B. Then Macro runs. And then I can't undo the copy).

Any way of avoiding this?