malik641
09-06-2005, 06:51 AM
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?
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?