VBA遍历Excel表格检测红色单元格及错误汇总表创建异常问题

Let's break down why your macro is showing the error alert even when there are no red cells, and fix it step by step:

Key Issues in Your Original Code #

• Unqualified Range Reference: The line Range("K" & Sheet1.usedRange.Rows.Count) doesn't specify it belongs to Sheet1. When Sheet1 isn't the active worksheet, this will reference the active sheet's K column instead, leading to incorrect range traversal (and potentially picking up red cells from another sheet by accident).
• Undeclared Variables: Acell and errorList are not explicitly declared, which can lead to unexpected behavior in VBA.
• Inefficient Worksheet Check: Your loop to delete the "Errors" sheet can be simplified and made safer.

Corrected Code #

Sub errorListCreation(Sheet1 As Worksheet)
    Dim isColored As Boolean
    Dim Acell As Range
    Dim errorSheet As Worksheet
    Dim targetRange As Range
    
    isColored = False
    
    ' Define the target range properly, fully qualified to Sheet1
    Set targetRange = Sheet1.Range("A2", Sheet1.Range("K" & Sheet1.UsedRange.Rows.Count))
    
    ' Check each cell in the target range
    For Each Acell In targetRange
        ' Use Interior.Color instead of DisplayFormat if you're setting the color directly via VBA
        ' If using conditional formatting to mark errors, keep DisplayFormat
        If Acell.Interior.Color = RGB(255, 0, 0) Then
            isColored = True
            Exit For ' No need to check further once a red cell is found
        End If
    Next Acell
    
    If isColored Then
        MsgBox "Validation errors found, please check the Errors sheet."
        
        ' Check if "Errors" sheet exists and delete it
        On Error Resume Next ' Ignore error if sheet doesn't exist
        Set errorSheet = ThisWorkbook.Worksheets("Errors")
        On Error GoTo 0
        
        If Not errorSheet Is Nothing Then
            Application.DisplayAlerts = False
            errorSheet.Delete
            Application.DisplayAlerts = True
        End If
        
        ' Create new Errors sheet
        ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = "Errors"
    Else
        MsgBox "Validation complete, no errors found."
    End If
End Sub

What Changed & Why #

1. Fully Qualified Range: We now explicitly set targetRange using Sheet1 for both ends of the range, ensuring we only check cells in the specified worksheet.
2. Explicit Variable Declarations: Added Dim Acell As Range and Dim errorSheet As Worksheet to avoid implicit type issues that can cause bugs.
3. Safer Worksheet Check: Used On Error Resume Next to safely check for the existence of the "Errors" sheet instead of looping through all worksheets. This is more efficient and cleaner.
4. Clarified Color Check: Added a comment about using Interior.Color vs DisplayFormat.Interior.Color — if your validation macro directly sets the cell's interior color (not via conditional formatting), Interior.Color is more reliable. If you're using conditional formatting to mark errors, keep DisplayFormat.Interior.Color.

Testing Tips #

• Verify that Sheet1 is the correct worksheet being passed to the macro when you call this subroutine.
• Double-check that your validation macro is setting cells to exactly RGB(255,0,0) (not a similar red shade like the default conditional format's light red, RGB(255,199,206)).

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