Borders With Inside Lines Macro

[miketurn]

Hello Everyone,

I would imagine this to be a very easy fix for the skilled members of this forum, but I personally do not know how to do so.

I created the following simple macro that applies border lines to select cells.
It adds a border around the edges, as well as adds border lines to vertical cell lines (NOT horizontal lines) within the selection.
This tools works fines, the only issue is that if you only select a single COLUMN to add a border around, because their are no "inside" lines to add borders to, it produces an error....

Run-time error '1004':
Unable to set the LineStyle property of the Border class

Can someone edit my following macro, so that it can ignore trying to add the "inside" lines when not present in the selection?

Sub Border_Inside&Out() 
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone 
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone 
    With Selection.Borders(xlEdgeLeft) 
        .LineStyle = xlContinuous 
        .Weight = xlMedium 
        .ColorIndex = xlAutomatic 
    End With 
    With Selection.Borders(xlEdgeTop) 
        .LineStyle = xlContinuous 
        .Weight = xlMedium 
        .ColorIndex = xlAutomatic 
    End With 
    With Selection.Borders(xlEdgeBottom) 
        .LineStyle = xlContinuous 
        .Weight = xlMedium 
        .ColorIndex = xlAutomatic 
    End With 
    With Selection.Borders(xlEdgeRight) 
        .LineStyle = xlContinuous 
        .Weight = xlMedium 
        .ColorIndex = xlAutomatic 
    End With 
    With Selection.Borders(xlInsideVertical) 
        .LineStyle = xlContinuous 
        .Weight = xlMedium 
        .ColorIndex = xlAutomatic 
    End With 
End Sub

Thank You

 

[MARK858]

Test for the number of columns...

Sub Border_InsideOut()
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    If Selection.Columns.Count > 1 Then
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
    End If
End Sub

 

[miketurn]

D a m n that was fast! I didn't even have to leave the forum and come back.
I have tested it out and it works great!
Thank you so much for this update.

 

[MARK858]

You're welcome

 

[Biz]

Another possible solution.

Sub Test()
Dim myBorders() As Variant, item As Variant
 myBorders = Array(xlEdgeLeft, _
 xlEdgeTop, _
 xlEdgeBottom, _
 xlEdgeRight, _
 xlInsideVertical)
  
'<~~ Remove Boarders
For Each item In myBorders
 With Selection.Borders(item)
 .LineStyle = xlNone
 End With
Next item
 
'<~~ Add Boarders
For Each item In myBorders
With Selection.Borders(item)
 .LineStyle = xlContinuous
 .Weight = xlMedium
 .ColorIndex = xlAutomatic
End With
Next item
End Sub

 

[miketurn]

@Biz
Thank you for your response, when I gave it a try your macro showed the same "1004" mentioned above, when it is applied to a single column that contains no inside lines?

 

[Peter_SSs]

Test for the number of columns...

Sub Border_InsideOut()
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
 [COLOR="#FF0000"][B]   With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With[/B][/COLOR]
    If Selection.Columns.Count > 1 Then
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
    End If
End Sub

Suggestion: Replace all the red code with this single line

Selection.BorderAround xlContinuous, xlMedium, xlAutomatic

 

[Peter_SSs]

BTW, I don't get the error with your original code even if a single column is chosen. What version of Excel are you using?

Instead of testing for more than one column, I think this should also work for you

Selection.BorderAround xlContinuous, xlMedium, xlAutomatic
<del>With Selection.Borders(xlInsideVertical)</del>
With Selection.Borders(xlLeft)
  .LineStyle = xlContinuous
  .Weight = xlMedium
  .ColorIndex = xlAutomatic
End With

 

[MARK858]

Suggestion: Replace all the red code with this single line

Selection.BorderAround xlContinuous, xlMedium, xlAutomatic

I would have done the same if doing it myself but at the time was just seeing if the column test solved the issue the OP had that I wasn't getting

 

[miketurn]

@Peter_SSs
Thank You for the updates you provided, I went with the following code which works great

Sub Border_InsideOut()
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    [COLOR=#0000CD][B]Selection.BorderAround xlContinuous, xlMedium, xlAutomatic[/B][/COLOR]
    If Selection.Columns.Count > 1 Then
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
    End If
End Sub

The update you mention with the following code does work, but this method only puts one line in the inside borders, as where the other method above overlaps them.

[COLOR=#0000CD][B]With Selection.Borders([U]xlLeft[/U])[/B][/COLOR]

I move cell information around a great deal and I am constantly dragging those moved cells borders along with that information, so having that overlap helps in my situation, so that I don't have to keep adding those missing border lines to the right and left of my cells back.

Using features such as borders and color fill backgrounds in general, I am constantly having to fix the cell formats to make them look pretty again and again. As I mention above with the undesired dragging of the borders and I also made a post a while back, about wishing there was a way to "CUT" cell data and background fill color, but also leave the same background fill color behind, instead of leaving a white cell that has to be re-filled with a background color.

https://www.mrexcel.com/forum/excel-questions/979939-cut-cell-data-but-dont-remove-fill-color.html

Anyways, thank you again for the help you have provided here, as well as all the help you have provided me in the past, it is very appreciated.