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)

 

[Akuini]

@luismoran, welcome to MrExcel.
Try this:
Put it in the sheet's code module.

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
            End If
    End If

End Sub

Edit:
You need to hit Enter or tab after typing the keyword.

 

[luismoran]

@luismoran, welcome to MrExcel.
Try this:
Put it in the sheet's code module.

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
            End If
    End If

End Sub

Edit:
You need to hit Enter or tab after typing the keyword.

Thank you for responding. I copied and paste but unfortunately, it did not work. I tried hitting tab or enter, tried using a different cell other than the one that has the conditional formatting, and also tried target.value but none of them worked. Is there something I have to do in the VBA interface to apply the code to the worksheet? I'm confident I have it in this sheet's module

 

[Akuini]

Not sure why that didn't work for you. The cursor is supposed to go to the found cell.
Maybe somehow Application.EnableEvents got turn off?
Try running this code first:

Sub toEnable()
Application.EnableEvents = True
End Sub

If that doesn't work, could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here

 

[luismoran]

Not sure why that didn't work for you. The cursor is supposed to go to the found cell.
Maybe somehow Application.EnableEvents got turn off?
Try running this code first:

Sub toEnable()
Application.EnableEvents = True
End Sub

If that doesn't work, could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here

Did not work as well. Your code seems very close to the solution I need, I appreciate your help. the conditional formatting still works but it is not scrolling to the exact row if the number is found in column A. Here is the link for the google drive. Let me know if it works.

Google Drive: Sign-in

Access Google Drive with a Google account (for personal use) or Google Workspace account (for business use).

drive.google.com

 

[Akuini]

I can't download the file, "Access denied".

I appreciate your help. the conditional formatting still works but it is not scrolling to the exact row if the number is found in column A.

Did the cursor move to the found cell?

 

[luismoran]

I can't download the file, "Access denied".


Did the cursor move to the found cell?

Sorry about that. Here is the unlisted version. The cursor did not move to the found cell.

Copy of Parts Finder.xlsm

PARTS PARTS FOR ASSEMBLY SEARCH:,16120 Assembly P/N,Retainer P/N, Tube P/N ,Plug P/N,Tube Braze Ring , Plug Braze Ring ,Plug Support,Tool Post,Gauge Pin,Box Partitions, Pcs/ box ,Customer 15999,16079,15998,16555,Ø.490 x .025,Ø.426 x .031 ,16317,15999,1.400" ,16120,270 16089,16094,16093,16091,Ø...

docs.google.com

 

[Akuini]

I downloaded the file, but the sheet is password protected.

 

[luismoran]

I downloaded the file, but the sheet is password protected.

I apologize once again, Password : dupage311. I didnt know the password carried on when i made a copy.

 

[Akuini]

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

Edit:
I added a message box if the code can't find the keyword:

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
            Else
                MsgBox "Cant't find " & Range("P2") & " in column A"
            End If
    End If

End Sub