PDA

View Full Version : Solved: Another border question...



malik641
08-01-2005, 09:16 AM
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
08-01-2005, 09:29 AM
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
08-01-2005, 09:32 AM
DRJ: is there some reason to specify .LineStyle and .ColorIndex? The values specified are the default values and should not be necessary.

Jacob Hilderbrand
08-01-2005, 09:37 AM
You can remove them if you just want the default.

malik641
08-01-2005, 09:43 AM
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
08-01-2005, 09:47 AM
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
08-01-2005, 10:20 AM
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!!!

xld
08-01-2005, 12:44 PM
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.