نگه داشتن فرمت و رنگ سلول‌ها پس از ترکیب آن‌ها در اکسل

Sub MergeFormatCell()
'pctarfand.ir & tarfandha.blog.ir'
 Dim xSRg As Range
 Dim xDRg As Range
 Dim xRgEachRow As Range
 Dim xRgEach As Range
 Dim xRgVal As String
 Dim I As Integer
 Dim xRgLen As Integer
 Dim xSRgRows As Integer
 Dim xAddress As String
 On Error Resume Next
 xAddress = ActiveWindow.RangeSelection.Address
 Set xSRg = Application.InputBox("Please select cell columns to concatenate:", "KuTools For Excel", xAddress, , , , , 8)
 If xSRg Is Nothing Then Exit Sub
 xSRgRows = xSRg.Rows.Count
 Set xDRg = Application.InputBox("Please select cells to output the result:", "KuTools For Excel", , , , , , 8)
 If xDRg Is Nothing Then Exit Sub
 Set xDRg = xDRg(1)
 For I = 1 To xSRgRows
  xRgLen = 1
  With xDRg.Offset(I - 1)
   .Value = vbNullString
   .ClearFormats
   Set xRgEachRow = xSRg(1).Offset(I - 1).Resize(1, xSRg.Columns.Count)
   For Each xRgEach In xRgEachRow
    .Value = .Value & Trim(xRgEach.Value) & " "
   Next
   For Each xRgEach In xRgEachRow
    xRgVal = xRgEach.Value
    With .Characters(xRgLen, Len(Trim(xRgVal))).Font
    .Name = xRgEach.Font.Name
    .FontStyle = xRgEach.Font.FontStyle
    .Size = xRgEach.Font.Size
    .Strikethrough = xRgEach.Font.Strikethrough
    .Superscript = xRgEach.Font.Superscript
    .Subscript = xRgEach.Font.Subscript
    .OutlineFont = xRgEach.Font.OutlineFont
    .Shadow = xRgEach.Font.Shadow
    .Underline = xRgEach.Font.Underline
    .ColorIndex = xRgEach.Font.ColorIndex
    End With
    xRgLen = xRgLen + Len(Trim(xRgVal)) + 1
   Next
  End With
 Next I
End Sub
 Dim xRg As Range
 Dim xAddress As String
 Dim I, xNum, xLastRow, xFstRow, xCol, xCount As Long
 On Error Resume Next
 xAddress = ActiveWindow.RangeSelection.Address
 Set xRg = Application.InputBox("Select a range to use(single column):", "KuTools For Excel", xAddress, , , , , 8)
 If xRg Is Nothing Then Exit Sub
 Application.ScreenUpdating = False
 xLastRow = xRg(1).End(xlDown).Row
 xFstRow = xRg.Row
 xCol = xRg.Column
 xCount = xRg.Count
 Set xRg = xRg(1)
 For I = xLastRow To xFstRow Step -1
  xNum = Cells(I, xCol)
  If IsNumeric(xNum) And xNum  0 Then
   Rows(I + 1).Resize(xNum).Insert
   xCount = xCount + xNum
  End If
 Next
 xRg.Resize(xCount, 1).Select
 Application.ScreenUpdating = True
End Sub

2) روی دکمه کلیک کنید یا کلید F5 را فشار دهید. با انجام اینکار پنجره زیر ظاهر می‌شود. سپس محدوده‌ای که می‌خواهید ترکیب کنید را انتخاب کنید.