A toggling border macro

T

Tom

On selecting an entire row, how to create a macro (which is to be attached
to a button) that colours the top and bottom borders light red. Upon
reclicking (toggling) the button the coloured borders disappear. This
process is to repeat itself. Thanks for any help.

TIA
Tom
 
S

Sheeloo

Attach the macro given below to a button and test...
to test select any row and press once to get borders and again to clear...
it will toggle the top and bottom borders for the current selection

Sub toggleBorders()
If Selection.Borders(xlEdgeTop).LineStyle = xlNone Then
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 3
End With
ElseIf Selection.Borders(xlEdgeTop).LineStyle = xlContinuous Then
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Else
'do nothing
End If
End Sub
 
D

Don Guillett

A bit of a variation.

Sub toggleborders()
With Selection.EntireRow
If .Borders.LineStyle = xlNone Then
myBorders = Array(, xlEdgeTop, xlEdgeBottom)
For i = 1 To UBound(myBorders)
With .Borders(myBorders(i))
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 3
End With
Next
Else
..Borders.LineStyle = xlNone
End If
End With
End Sub
 
T

Tom

It works beautifully. Thanks Don. One question, lets say if I wanted to
limit the length of the row to only 40 cells, how do you modify the codes?
Much appreciate for your help.

Tom
 
T

Tom

That is after selecting the entire row, then limit the top and bottom
borders
to 40 column cells along that row.
 
D

Don Guillett

Change to
Sub toggleborders()
'With Selection.EntireRow
With Cells(ActiveCell.Row, 1).Resize(, 40)
If .Borders.LineStyle = xlNone Then
myBorders = Array(, xlEdgeTop, xlEdgeBottom)
For i = 1 To UBound(myBorders)
With .Borders(myBorders(i))
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 3
End With
Next
Else
..Borders.LineStyle = xlNone
End If
End With
End Sub
 
T

Tom

Yes, it is very versatile and also clears up as long as the the active cell
is anywhere along the original row. Thanks once again for your help, Don.

Tom
 
Top