Is there a way to see if two cells match using a constantly running macro? Then to scroll to that exact row is appeared on?

[luismoran]

This is what the excel sheet looks like, with my search cell in P2 and my area I want to search in column A only.



I'm using conditional formatting to highlight the row the number is found on. But I would also like to use VBA to see if cell P2 matches any cell in column A, and if they do then to scroll to that row. Essentially I want the user to simply head to the search bar (P2), type in the Assembly part they're looking for (numbers in Column A) and if its found, then scroll directly to the row.

I want this macro to be in the background of this worksheet, without having to be able to click any buttons to perform these actions.

After reading a couple posts here I think the following line of code is a great start but not sure where to go after this. Would appreciate any help, thanks.

Private Sub Worksheet_Change(ByVal Target as Range)

 

[luismoran]

You put the code in the wrong module, it should be in sheet PARTS module.
Remove the code in module1.
Click sheet PARTS tab > select View Code > paste the code

And I added ActiveWindow.ScrollColumn = 1:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$P$2" Then
        Dim c As Range
        Set c = Range("A:A").Find(What:=Target, LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            If Not c Is Nothing Then
                c.Activate
                    ActiveWindow.ScrollColumn = 1
            End If
    End If

End Sub

AHH, great I see the cursor now. Thank you for your help with that. Much appreciated.
Is there a way to scroll the rows up/down to where the cursor is at?

 

[Akuini]

AHH, great I see the cursor now. Thank you for your help with that. Much appreciated.
Is there a way to scroll the rows up/down to where the cursor is at?

Try:
I also add a line to show a message box if the keyword is not found.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$P$2" Then
        Dim c As Range
        Set c = Range("A:A").Find(What:=Target, LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            If Not c Is Nothing Then
                c.Activate
                    ActiveWindow.ScrollColumn = 1
                    ActiveWindow.ScrollRow = c.Row
            Else
                MsgBox "Cant't find " & Range("P2") & " in column A"
            End If
    End If

End Sub

 

[Akuini]

I just read this part in your file:
"The box above is to search for P/N of:
Tubes, Plugs, Retainers, Assemblies
and braze ring I.D. or thickness."

If that's really what you're doing, then I have a suggestion:
Use 2 comboboxes to search, the first is to select a category/column and the second is to search for items. And the second combobox has a searchable feature, so for example if you type "17" then all items starting with "17" will be listed in the combobox to choose from. So the process will be easier & faster.

 

[luismoran]

Try:
I also add a line to show a message box if the keyword is not found.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$P$2" Then
        Dim c As Range
        Set c = Range("A:A").Find(What:=Target, LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            If Not c Is Nothing Then
                c.Activate
                    ActiveWindow.ScrollColumn = 1
                    ActiveWindow.ScrollRow = c.Row
            Else
                MsgBox "Cant't find " & Range("P2") & " in column A"
            End If
    End If

End Sub

You are a King. This is exactly what I needed. Thank you, God bless you.
Personal Question: How did you learn VBA so well?

I just read this part in your file:
"The box above is to search for P/N of:
Tubes, Plugs, Retainers, Assemblies
and braze ring I.D. or thickness."

If that's really what you're doing, then I have a suggestion:
Use 2 comboboxes to search, the first is to select a category/column and the second is to search for items. And the second combobox has a searchable feature, so for example if you type "17" then all items starting with "17" will be listed in the combobox to choose from. So the process will be easier & faster.

Thanks, I appreciate the suggestion and Ill look into combo boxes. Just started using VBA a couple weeks ago, everything is pretty interesting to say the least

 

[Akuini]

How did you learn VBA so well?

Well, mostly from this forum.