如何在Excel中合并带有不同颜色内容的单元格？

Scenario 1: Preserve Original Text Colors (Rows 2-8) #

Excel’s built-in formulas can’t retain font color during merging. Use this VBA macro to automate the process:

Sub MergeWithOriginalColors()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim rowNum As Integer, colNum As Integer
    Dim targetCell As Range, sourceCell As Range
    Dim charPos As Integer
    
    ' Process rows 2 to 8
    For rowNum = 2 To 8
        Set targetCell = ws.Cells(rowNum, "E")
        targetCell.ClearContents
        charPos = 1
        
        For colNum = 1 To 4 ' Columns A-D
            Set sourceCell = ws.Cells(rowNum, colNum)
            If sourceCell.Value <> "" Then
                targetCell.Value = targetCell.Value & sourceCell.Value
                ' Apply original color to the appended text segment
                targetCell.Characters(Start:=charPos, Length:=Len(sourceCell.Value)).Font.Color = sourceCell.Font.Color
                charPos = charPos + Len(sourceCell.Value)
            End If
        Next colNum
    Next rowNum
End Sub

How to use:

• Press Alt+F11 to open the VBA Editor
• Insert a new module (right-click your workbook > Insert > Module)
• Paste the code above
• Run the macro (F5 or via the Run button)

Scenario 2: Apply Custom Colors to Merged Text (Rows 9-15) #

Use this VBA macro to assign specific colors to each column’s content during merging:

Sub MergeWithCustomColors()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim rowNum As Integer, colNum As Integer
    Dim targetCell As Range, sourceCell As Range
    Dim charPos As Integer
    ' Define custom colors for columns A-D (adjust RGB values as needed)
    Dim columnColors(1 To 4) As Long
    columnColors(1) = RGB(255, 0, 0)    ' Red for column A
    columnColors(2) = RGB(0, 255, 0)    ' Green for column B
    columnColors(3) = RGB(0, 0, 255)    ' Blue for column C
    columnColors(4) = RGB(128, 0, 128)  ' Purple for column D
    
    ' Process rows 9 to 15
    For rowNum = 9 To 15
        Set targetCell = ws.Cells(rowNum, "E")
        targetCell.ClearContents
        charPos = 1
        
        For colNum = 1 To 4 ' Columns A-D
            Set sourceCell = ws.Cells(rowNum, colNum)
            If sourceCell.Value <> "" Then
                targetCell.Value = targetCell.Value & sourceCell.Value
                ' Apply custom color to the appended text segment
                targetCell.Characters(Start:=charPos, Length:=Len(sourceCell.Value)).Font.Color = columnColors(colNum)
                charPos = charPos + Len(sourceCell.Value)
            End If
        Next colNum
    Next rowNum
End Sub

Customization: Modify the RGB() values in the columnColors array to match your desired colors.

HTML to Excel Format Conversion #

If you generated formatted HTML from your CSV data, use this VBA macro to convert HTML into styled Excel cells:

Sub ConvertHTMLToFormattedText()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim rowNum As Integer
    Dim htmlCell As Range, targetCell As Range
    
    ' Assume HTML content is in column F, target is column E (adjust as needed)
    For rowNum = 2 To 15
        Set htmlCell = ws.Cells(rowNum, "F")
        Set targetCell = ws.Cells(rowNum, "E")
        
        If htmlCell.Value <> "" Then
            ' Parse HTML content
            Dim htmlDoc As Object
            Set htmlDoc = CreateObject("htmlfile")
            htmlDoc.body.innerHTML = htmlCell.Value
            
            ' Copy formatted text to target cell
            targetCell.Value = htmlDoc.body.innerText
            Dim objRange As Object
            Set objRange = htmlDoc.body.createTextRange()
            objRange.execCommand "Copy"
            targetCell.PasteSpecial Paste:=xlPasteFormats
        End If
    Next rowNum
End Sub

How to use:

1. Paste your generated HTML into a helper column (e.g., column F)
2. Run the macro to transfer formatted text into column E

内容的提问来源于stack exchange，提问作者jamyandy_1500