Macro to copy rows based on background color of cells within a range of columns

First I want to thank everyone in the forums for taking the time to help out others!!

After quite exhaustive searching this macro is the closest to what I need, so I thought I would ask for a little help.

I ran this macro but haven't figured out how to get the final touches on it.
A little background. I have an inventory with multiple worksheets, but not every worksheet needs to have rows copied. I am collecting all missing items on it's own worksheet. Missing items are highlighted red using a dropdown box with conditional formatting in a "status" column.

So what I need:
I need to specify multiple worksheets to search (ex. names SN06210, SN06255, etc)
I need to specify which worksheet to paste the data (Discrepancies)


And just as a question about macros in general- Will it update as people make changes or do I need to manually run the macro every time?

Here's a sample file.
Attachment 279328

Thank you so much!!

Oh boy. Well now that I added the dropdown with conditional formatting the macro doesn't work anymore.

I think this might work, but I need it to say the equivalent of

WITH Application.Find
Clear
Interior.Characters = Missing

But obviously, that's not the way to write it. LOL

Sub test()
    Dim r As Range, ff As String, x As Range
    With Application.FindFormat
        .Clear
        .Interior.Color = 255
    End With
    With Sheets("SN06209").Columns(9)
        Set r = .Find("*", searchformat:=True)
        If Not r Is Nothing Then
            ff = r.Address: Set x = r
            Do
                Set x = Union(x, r)
                Set r = .Find("*", r, searchformat:=True)
            Loop Until ff = r.Address
            x.EntireRow.Copy Sheets("Discrepancies").Cells(1)
        End If
    End With
End Sub