VBA技术求助：在所有工作表中批量修改指定单元格文本格式

Hey there! I see you're new to VBA and hitting some roadblocks with your worksheet loop code—let's fix this together. Your code has a few structural and logical issues that are causing errors, so let's break them down and adjust the code step by step.

Key Issues in Your Original Code #

• Incomplete Loop Structure: You started a Do loop for finding multiple matches but never added a corresponding Loop statement, and missed closing End If before Next I—this is a critical syntax error that breaks compilation.
• Unspecified Worksheet: Cells.Find defaults to the active worksheet instead of the one you're looping through (Worksheets(I)), which means your code won't check every sheet as intended.
• Uninitialized Variable: intPos isn't reset to 0 when moving to a new cell, so subsequent text searches will start from the last position instead of the beginning of the cell.
• Redundant Assignment: You set varSearch = "CUS_ECO_SEC_CD" inside the loop, which repeats the same value assignment unnecessarily.

Corrected Code #

Sub WorksheetLoop()
    Dim WS_Count As Integer
    Dim I As Integer
    Dim varFound As Range, varSearch As String
    Dim strAddress As String, intPos As Integer
    
    ' Define search term once outside the loop
    varSearch = "CUS_ECO_SEC_CD"
    WS_Count = ActiveWorkbook.Worksheets.Count
    
    ' Loop through each worksheet in the workbook
    For I = 1 To WS_Count
        With ActiveWorkbook.Worksheets(I)
            ' Find the first occurrence of the search term in the current sheet
            Set varFound = .Cells.Find(varSearch, LookIn:=xlValues, LookAt:=xlPart)
            
            If Not varFound Is Nothing Then
                strAddress = varFound.Address ' Store first cell address to avoid infinite loop
                
                ' Loop through all matching cells in the sheet
                Do
                    intPos = 0 ' Reset position counter for each new cell
                    With varFound
                        ' Highlight all instances of the search term inside the cell
                        Do
                            intPos = InStr(intPos + 1, .Value, varSearch, vbTextCompare)
                            If intPos > 0 Then
                                .Characters(Start:=intPos, Length:=Len(varSearch)).Font.ColorIndex = 4
                            End If
                        Loop Until intPos = 0
                    End With
                    
                    ' Find the next matching cell
                    Set varFound = .Cells.FindNext(varFound)
                ' Stop when we loop back to the first matching cell
                Loop While Not varFound Is Nothing And varFound.Address <> strAddress
            End If
        End With
    Next I
End Sub

What Changed & Why #

• Explicit Range Type: Changed varFound from Variant to Range for clearer type safety and better readability.
• Worksheet Context: Used With ActiveWorkbook.Worksheets(I) to ensure all operations target the current sheet in the loop, not the active worksheet.
• Complete Loop Structure: Added Loop While to close the outer match-finding loop, and included FindNext to locate subsequent matches. Checking the cell address prevents an infinite loop.
• Reset intPos: Resetting intPos to 0 for each new cell ensures we start searching from the beginning of the cell every time.
• Optimized Assignment: Moved varSearch outside the loop to avoid redundant value sets.

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