Zeile suche, highlighten und danach loeschen

[Tigger86]

Guten Morgen, ich moechte gerne folgendes erreichen.. ich habe diesen code hier: 


soweit funktioniert fast alles.. aber ich moechte folgendes erreichen.. wenn die letzten 4 ziffern ausgewaelt wurden bzw. gesucht werden.. soll zu der zeile hingesprungen werden und die zeile von Spalte A bis H soll gehighlighted werden.. 
irgendwo ist da noch ein fehler drin. 

Code:

Sub HighlightAndDeleteLine()
    Dim SearchValue As String
    Dim Row As Long
    Dim LastRow As Long
    Dim Answer As VbMsgBoxResult ' Declare only once
    Dim ws As Worksheet
    Dim RowHighlighted As Long ' Variable to store the highlighted row
   
    ' Set the worksheet object (you can change this if needed)
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your actual sheet name
   
    ' Temporarily unprotect the sheet
    ws.Unprotect "60905857" ' Use your desired password
   
    ' Get the search value from the input prompt
    SearchValue = InputBox("Enter the last 4 digits of the stock number you want to search for:")
   
    ' If the search value is empty, exit the subroutine
    If SearchValue = "" Then
        MsgBox "Please enter a search value.", vbExclamation
        ' Reprotect the sheet before exiting
        ws.Protect "60905857" ' Use your desired password
        Exit Sub
    End If
   
    ' Determine the last row in column C (can be adjusted)
    LastRow = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row
   
    ' **Clear any previous highlights from A to H**
    ws.Range("A1:H" & LastRow).Interior.ColorIndex = -4142 ' Remove all highlights
   
    ' Search column C for the last digits of the search value
    For Row = 1 To LastRow
        If Right(ws.Cells(Row, 3).Value, Len(SearchValue)) = SearchValue Then
            ' Highlight the row from A to H by changing the background color
            ws.Range("A" & Row & ":H" & Row).Interior.Color = RGB(255, 255, 0) ' Yellow
            RowHighlighted = Row ' Store the highlighted row
           
            ' Jump to the highlighted row
            Application.Goto ws.Cells(Row, 1) ' This will jump to the first column of the found row
           
            ' Show the confirmation popup
            Answer = MsgBox("The row has been highlighted. Do you want to delete it now?", vbYesNo + vbQuestion, "Delete Line?")
           
            If Answer = vbYes Then
                ws.Rows(RowHighlighted).Delete
                RowHighlighted = 0 ' Reset the highlight after deletion
                MsgBox "The row has been deleted."
            Else
                ' If the operation is canceled, reset the highlight color
                ws.Range("A" & RowHighlighted & ":H" & RowHighlighted).Interior.ColorIndex = -4142 ' Default color (no color)
                MsgBox "The row has not been deleted."
            End If
           
            ' Reprotect the sheet before exiting
            ws.Protect "60905857" ' Use your desired password
            Exit Sub
        End If
    Next Row
   
    MsgBox "No match found."
   
    ' Reprotect the sheet after completing the task
    ws.Protect "60905857" ' Use your desired password
End Sub

  danke schonmal

[Klaus-Dieter]

Hallo,

bei mir funktioniert es.

[Tigger86]

ja...komischerweise geht es mal.. und mal geht es wieder erst nach dem dritten mal.. also unregelmassig..

[Klaus-Dieter]

Hallo,

das Makr erscheint mir für den Zweck allerdings sehr umfangreich zu sein. Das geht bestimmt einfacher.