Concatenate with style

[Abdulkhadar]

Hello Excel Genius, Below VBA was posted by Mr Rick Rothstein I want some change in this below VB. If any cell from A to F contains a formula like =Sheet1!A5&" "&Sheet1!C7 or =Sheet1!A5*3 etc it shows Runtime error '1004. How to solve this error.

Sub ConcatWithStyles()
  Dim X As Long, LastRow As Long, LastCol As String, Cell As Range, Position As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Application.ScreenUpdating = False
  For Each Cell In Range("A1:F" & LastRow)
    LastCol = Split(Cells(Cell.Row, "G").End(xlToLeft).Address, "$")(1)
    If Cell.Column <= Columns(LastCol).Column Then
      With Range("H1").Offset(Cell.Row - 1)
        If Cell.Column = 1 Then
          .ClearFormats
          Position = 1
          Range("H" & .Row).Value = Space(Evaluate(Replace("SUM(LEN(A#:" & LastCol & _
                                    "#))+COLUMNS(A#:" & LastCol & "#)-1", "#", .Row)))
        End If
        .Characters(Position, Len(Cell.Value)).Text = Cell.Characters(1, Len(Cell.Value)).Text
        For X = 1 To Len(Cell.Value)
          With .Characters(Position + X - 1, 1).Font
            .Name = Cell.Characters(X, 1).Font.Name
            .Size = Cell.Characters(X, 1).Font.Size
            .Bold = Cell.Characters(X, 1).Font.Bold
            .Italic = Cell.Characters(X, 1).Font.Italic
            .Underline = Cell.Characters(X, 1).Font.Underline
            .Color = Cell.Characters(X, 1).Font.Color
            .Strikethrough = Cell.Characters(X, 1).Font.Strikethrough
            .TintAndShade = Cell.Characters(X, 1).Font.TintAndShade
            .FontStyle = Cell.Characters(X, 1).Font.FontStyle
            .Superscript = Cell.Characters(X, 1).Font.Superscript
            .Subscript = Cell.Characters(X, 1).Font.Subscript
          End With
        Next
      End With
      Position = Position + Len(Cell.Value) + 1
    End If
  Next
  Application.ScreenUpdating = True
End Sub

Thanks in advance