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

 

[Peter_SSs]

.. I am constantly dragging those moved cells borders along with that information, so having that overlap helps in my situation,..

Yes, if you are dragging the cells around my second suggestion would not be so good. I didn't realise that you were doing that.
Anyway, sounds like you have ended up with a good outcome.

 

[miketurn]

Anyway, sounds like you have ended up with a good outcome.

Absolutely, and I thank you again for helping me arrive at that outcome

 

[Biz]

@ Miketurn
Can you please post the entire code which works for you? Just curious which code you are using.

Biz

 

[MARK858]

@ Miketurn
Can you please post the entire code which works for you? Just curious which code you are using.

Biz

Hi Biz, I think by his comment Miketurn is using the first code block in post number 10 i.e.

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

 

[miketurn]

@Big
MARK585 is correct, the code I pasted at the top of my post number 10 is the one.
Hope this helps

Again thank you to all of you for providing me we such great help.

 

[Biz]

I was thinking too many selections which are really not required see below.
Does the code still work?

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

 

[miketurn]

@Biz
Thank you for your response, I gave it a try and it works great as well.

 

[Biz]

@Biz
Thank you for your response, I gave it a try and it works great as well.

Thank you for the feedback.

 

[miketurn]

I apologize for opening this thread back up, but from time to time I have been messing around with the macros that very generous forum members have provided for me, just to see if I can slightly tweak them and learn what each line of code does.
I have been getting slightly better (meaning I don't see that "debug" popup dialog box as much ) but sometimes the simplist things trip me up and I was wondering if somebody could help a bit, should be very simple.

I figured I would try to see if I can tweak this macro, so instead of it producing a border around the entire selection, it would just add a border to the right and left sides, and would include column vertical lines inside the selection as well. As mentioned in the original macro I also wanted it to double up (ovelap) the interior verical lines.

I used the following macro to edit, just because it has some easier elements to understand.


Same as my original macro, this macro works perfect, accept when you try to run it on a selection that only contains one column.

Sub Border_TEST1()
    With Selection.Borders(xlEdgeLeft)
        .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

I then tried to replace the xlInsideVertical code with the "If" piece of code provided.

Sub Border_TEST2()
    With Selection.Borders(xlEdgeLeft)
        .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

When run, I get an error and the debug highlights the following line of code

 If Selection.Columns.Count > 1 Then

Can some explain why that is.
As mentioned I chose this code because it was easier to breakdown, but if someone wants to show the edits done to @Biz macro from his post number 16 that would be fine to.

Sorry for the long explanation, but wanted to explain my attempts / learning process.
Thank You to anyone who reads this

 

[miketurn]

Ahhh d a m n time limit to edit posts, I figured it out.