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
 
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
 
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

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.
 
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
 
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
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Create new border style? 6
Border formatting row of cells 22
Adding a coloured border one the outside of a one row-two column t 4
using border/usedrange in vba 0
Can excel run a batch file? 2
Conditional Formatting based on dynamic Date 1
looping macro to test for borders 9
Conditional Formatting Help 4

Top