Another border question...

[malik641]

How can I make this to work when I select a value from a Data list??
If I can't, I'll just use conditional formatting :p

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" & 1).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

[Jacob Hilderbrand]

Something like this?


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
With Range("A" & Target.Row - 1).EntireRow
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
End If
End Sub

[MWE]

DRJ: is there some reason to specify .LineStyle and .ColorIndex? The values specified are the default values and should not be necessary.

[Jacob Hilderbrand]

You can remove them if you just want the default.

[malik641]

uuuuuuuuuuuuuuuuuuuuuh...nevermind....It's working just the way it is:dunno :dunno :dunno

No idea what was wrong before....?????
But thanks DRJ and MWE.

Sorry for the weird mix-up

[Norie]

Joseph

If by data list you mean one created by Data>Validation then I'm afraid making a choice from it will not trigger the change event. (I believe that's been fixed in later versions though)

[malik641]

Joseph

If by data list you mean one created by Data>Validation then I'm afraid making a choice from it will not trigger the change event. (I believe that's been fixed in later versions though)Yeah that's what I meant...and I thought the same thing....but it works!!!

[Bob Phillips]

Yeah that's what I meant...and I thought the same thing....but it works!!!

Not in Excel97, you need to use the Calculate event.