VBA实现高亮首列表中未在次列表出现的单元格报错求助

Let's break down what's going wrong with your code and fix it step by step:

Key Issues in Your Current Code #

• Wrong parameter for Application.Match: You're passing v2.Address (a string of the range's address) instead of the actual v2 range object. Match needs a range or array to search through, not a text string—this is the root cause of Error 2015.
• Incorrect row count logic: v1.End(xlDown).Row can fail if there are blank cells in your first list, or if the list doesn't start at row 1. Since you already have a Range object (v1), you can directly loop through its cells instead of relying on row numbers.
• Reversed logic: You're highlighting cells that are found in the second list, but you want to highlight cells that are not present.
• Data type mismatch: f must be a Variant because Match returns either a numeric position or an error value—using String or Long will throw a type mismatch error.

Corrected Code #

Sub ColorCells()
    Dim v1 As Range
    Dim v2 As Range
    Dim cell As Range
    Dim f As Variant
    
    ' Get user-selected ranges, handle cancel button
    On Error Resume Next
    Set v1 = Application.InputBox("Select the first list", "Choose Range", Type:=8)
    Set v2 = Application.InputBox("Select the second list", "Choose Range", Type:=8)
    On Error GoTo 0
    
    ' Exit if user canceled selection
    If v1 Is Nothing Or v2 Is Nothing Then Exit Sub
    
    ' Clear existing fill color first (optional but clean)
    v1.Interior.ColorIndex = xlColorIndexNone
    
    ' Loop through each cell in the first list
    For Each cell In v1
        ' Search for the cell value in the second list
        f = Application.Match(cell.Value, v2, 0)
        
        ' Highlight red if the value is NOT found
        If IsError(f) Then
            cell.Interior.ColorIndex = 3 ' 3 corresponds to red
        End If
    Next cell
End Sub

What Changed & Why #

1. Used For Each cell In v1: This safely loops through every cell in the user-selected range, regardless of where it starts or if there are gaps in the list.
2. Passed v2 (range object) to Match: Fixes Error 2015 by giving Match the correct data type to search through.
3. Reversed the condition: Now we check If IsError(f)—this triggers when the value isn't found in the second list, which aligns with your requirement.
4. Added cancel button handling: Prevents runtime errors if the user clicks Cancel instead of selecting a range.
5. Cleared existing color: Ensures old highlights are removed before applying new ones (you can remove this line if you want to retain previous formatting).

Additional Notes #

• This works for both numeric values and 10-digit text strings—Application.Match handles both types correctly as long as values are formatted consistently (e.g., no extra spaces in text strings).
• For very large datasets, consider using a Dictionary object for faster searches, but Match is sufficient for most standard use cases.

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