Excel: restore (or don't cut) borders from cells during cut and paste

Question

In an Excel sheet I want to cut cells and paste them to another location of the same sheet but the borders in the original cells are lost in the process. I'm trying to restore the borders with these VBA subs in the sheet but my code doesn't restore thick borders and creates diagonal borders that don't exist in the original cells.

Private originalBorders As Collection

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' Check if the selection change is due to Cut operation
    If Application.CutCopyMode = xlCut Then
        ' Store original borders of the selected cells
        Set originalBorders = New Collection
        SaveBorders originalBorders, Target
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Check if originalBorders is set
    If Not originalBorders Is Nothing Then
        ' Reapply original borders to the destination cells after paste operation
        RestoreBorders originalBorders, Target
        ' Clear originalBorders
        Set originalBorders = Nothing
    End If
End Sub

Private Sub SaveBorders(ByRef bordersCollection As Collection, ByVal rng As Range)
    ' Save the border settings of the given range
    Dim border As Object
    For Each border In rng.Borders
        bordersCollection.Add border.LineStyle
        bordersCollection.Add border.Color
        bordersCollection.Add border.TintAndShade
        bordersCollection.Add border.Weight
    Next border
End Sub

Private Sub RestoreBorders(ByRef bordersCollection As Collection, ByVal rng As Range)
    ' Restore the border settings to the given range
    Dim borderIndex As Integer
    Dim border As Object
    For Each border In rng.Borders
        border.LineStyle = bordersCollection(borderIndex + 1)
        border.Color = bordersCollection(borderIndex + 2)
        border.TintAndShade = bordersCollection(borderIndex + 3)
        border.Weight = bordersCollection(borderIndex + 4)
        borderIndex = borderIndex + 4
    Next border
End Sub

Answer 1

Assuming users try to cut cell A1 and paste on cell D1.

• Worksheet_SelectionChange is activated when users select D1 (before pasting). Application.CutCopyMode is xlCut after pressing Ctrl+X. SaveBorders() is called to save borders' infor before pasting. Target is D1 instead of A1. That is, originalBorders stores the infor of destination cell before pasting. The thick borders isn't stored in originalBorders.

• If LineStyle is set to xlNone, the border should be hidden/removed on the source cell. However the next line (set Color=0) adds the border line. That's the reason why the code doesn't restore thick borders and creates extra diagonal borders.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' Check if the selection change is due to Cut operation
    If Not Application.CutCopyMode = xlCut Then ' **
        ' Store original borders of the selected cells
        Set originalBorders = New Collection
        SaveBorders originalBorders, Target
    End If
End Sub

Private Sub RestoreBorders(ByRef bordersCollection As Collection, ByVal rng As Range)
    ' Restore the oBorder settings to the given range
    Dim borderIndex As Integer
    Dim oBorder As border
    Debug.Print "restore " & rng.Address
    For Each oBorder In rng.Borders
        oBorder.LineStyle = bordersCollection(borderIndex + 1)
        If bordersCollection(borderIndex + 1) <> xlNone Then ' **
            oBorder.Color = bordersCollection(borderIndex + 2)
            oBorder.TintAndShade = bordersCollection(borderIndex + 3)
            oBorder.Weight = bordersCollection(borderIndex + 4)
        End If
        borderIndex = borderIndex + 4
    Next oBorder
End Sub