如何通过Visual Basic为包含红色文本（或非默认黑色文本）单元格的Excel行应用格式

Absolutely! This is such a common headache when dealing with tracked changes in Excel—manual scanning is error-prone and tedious. VBA is perfect for automating this, so you’ll never miss a red-text cell again. Here’s a robust, customizable solution tailored to your needs:

Sub HighlightRowsWithColoredText()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim i As Long, j As Long
    Dim hasColoredText As Boolean
    
    ' Target the worksheet you need to process (update to your sheet name)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Get the last used row and column to avoid scanning empty cells
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    ' Loop through each row (start at row 2 if you have a header row)
    For i = 1 To lastRow
        hasColoredText = False
        
        ' Check every cell in the current row
        For j = 1 To lastCol
            ' Look for cells with non-default black text
            ' Adjust RGB(0,0,0) if your default text color is different
            If ws.Cells(i, j).Font.Color <> RGB(0, 0, 0) Then
                hasColoredText = True
                Exit For ' No need to check other cells in this row
            End If
        Next j
        
        ' Apply formatting if the row has colored text
        If hasColoredText Then
            ' Example: Light yellow fill + bold first cell
            ws.Rows(i).Interior.Color = RGB(255, 255, 153)
            ws.Cells(i, 1).Font.Bold = True
            ' Add more formatting here if needed:
            ' ws.Rows(i).Borders.LineStyle = xlContinuous
            ' ws.Rows(i).Font.Color = RGB(128, 0, 0)
        Else
            ' Optional: Clear formatting for rows with no colored text
            ws.Rows(i).Interior.Color = xlNone
            ws.Cells(i, 1).Font.Bold = False
        End If
    Next i
    
    MsgBox "Row highlighting finished!", vbInformation
End Sub

Customization & Usage Tips: #

• Worksheet Target: Replace "Sheet1" with the actual name of your worksheet (e.g., "DataChanges").
• Default Text Color: If your Excel uses a non-black default text color, run ?ActiveCell.Font.Color in the VBA Immediate Window (press Ctrl+G to open it) while selecting a default cell, then replace RGB(0,0,0) with that value.
• Formatting Tweaks: Adjust the Interior.Color to any shade you prefer (e.g., RGB(255, 204, 204) for light red) or add border styles, font changes, etc., to match your workflow.
• Header Rows: If your data starts at row 2 (with headers in row 1), change the loop start from 1 to 2.

How to Run the Macro: #

1. Open your Excel file with the tracked changes
2. Press Alt + F11 to open the VBA Editor
3. Right-click your workbook in the left Project Explorer pane > Insert > Module
4. Paste the code into the new module
5. Press F5 to run it, or assign it to a button in Excel for one-click access

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