Excel VBA问题：选中条目点击继续高亮目标行遇运行时错误1004

Hey there! Let's break down what's going on here and fix that 1004 error, plus get your row highlighting working properly for cells with multi-line entries.

First, let's unpack the error you're seeing:

• The WorksheetFunction.Match method throws a 1004 error when it can't locate a matching value in your search range. Unlike Application.Match (which returns an error value instead of crashing), this method will fail hard if no match exists.
• Since your cells contain two entries split by vbNewLine, you're likely trying to match a single selected entry against the full cell content (including the newline and second item) — so Match can't find a match, hence the crash.

Here's a robust solution that handles both the error and multi-line cell content:

Step 1: Handle Invalid Inputs & Error Cases #

First, we'll add checks to make sure the user selects a valid entry, and avoid crashes when no match is found.

Step 2: Search Within Multi-Line Cells #

We'll split each cell's content by vbNewLine to check if your selected entry exists anywhere in the cell, then highlight the entire row if it does.

Full Working Code Example #

Sub HighlightRowBySelectedEntry()
    Dim selectedText As String
    Dim targetSheet As Worksheet
    Dim searchColumn As Range
    Dim cell As Range
    Dim cellEntries As Variant
    Dim entryIndex As Integer
    
    ' Validate user selection first
    If TypeName(Selection) <> "Range" Or Selection.Cells.Count > 1 Then
        MsgBox "Please select a single cell with the entry you want to find!", vbExclamation
        Exit Sub
    End If
    selectedText = Trim(Selection.Value)
    If selectedText = "" Then
        MsgBox "Selected cell is empty — pick a cell with an entry!", vbExclamation
        Exit Sub
    End If
    
    ' Set your target sheet and search range (adjust these to your workbook)
    Set targetSheet = ThisWorkbook.Worksheets("Sheet1") ' Replace with your sheet name
    Set searchColumn = targetSheet.Range("A2:A100") ' Replace with your column/range to scan
    
    ' Clear old highlights first (keeps things clean)
    targetSheet.Cells.Interior.ColorIndex = xlColorIndexNone
    
    ' Loop through each cell to check for matches
    For Each cell In searchColumn
        If Not IsEmpty(cell.Value) Then
            ' Split the cell's content into individual entries using vbNewLine
            cellEntries = Split(cell.Value, vbNewLine)
            
            ' Check if the selected entry matches any item in the cell
            For entryIndex = LBound(cellEntries) To UBound(cellEntries)
                If Trim(cellEntries(entryIndex)) = selectedText Then
                    ' Highlight the entire row with a light yellow (adjust color as needed)
                    cell.EntireRow.Interior.Color = RGB(255, 255, 153)
                    Exit For ' No need to check other entries in this cell
                End If
            Next entryIndex
        End If
    Next cell
    
    MsgBox "Done! Rows containing your selected entry are highlighted.", vbInformation
End Sub

Key Improvements: #

• Input Validation: Guides the user to select a valid single cell, avoiding messy invalid inputs.
• Multi-Line Support: Uses Split() to break apart cells with vbNewLine-separated entries, so we can match against individual items instead of the full cell content.
• Clean Highlighting: Clears existing highlights before applying new ones, so you don't get leftover colors from previous runs.
• User Feedback: Shows clear messages to keep the user informed.

How to Use: #

1. Select the cell with the entry you want to search for.
2. Assign this macro to your "Continue" button (right-click the button > Assign Macro > select HighlightRowBySelectedEntry).
3. Click the button to trigger the highlighting.

If you were originally using Match and just want to fix that specific error (for single-line cells), here's a quick adjustment:

Dim matchResult As Variant
matchResult = Application.Match(selectedText, searchColumn, 0) ' 0 = exact match

If IsError(matchResult) Then
    MsgBox "No matching entry found!", vbInformation
Else
    targetSheet.Rows(matchResult + searchColumn.Row - 1).Interior.Color = RGB(255, 255, 153)
End If

Note: This simpler version only works if your search cells contain exactly the selected entry (not multi-line content). Use the full code example above for cells with vbNewLine splits.

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