I wonder if you could have conditional formatting do some of this. It seems to me that even if not once the data has been displayed on your Form, the values in the various TextBoxes could be programmatically reviewed and compared to other data as the need requires and formatting cues -- such as backColor, ForeColor, Alignment, Font Style etc. . . -- could be assigned. My impression is that you will find Access MORE flexible than Excel in this regard.

I tried Conditional formatting using expression is: [Part_Number] = [Scan] but it does not work like this i want to go scan scan scan and the matching items get colored and count the colored items. but conditional formating does a single entry.

I see your point. It's a bit of a task, for sure. Corresponding to the "columns" in an Excel Worksheet, you will have an Access data table. That's probably the best place to "scan" via a query. If your query includes both the table with the data to be accessed and a second table containing the values against which your comparing, you'd have to work out some way to "flag" matches. Maybe a field added to the original assessed data table into which an updateQuery could place a code which your display form could use as a guide for creating the formats. I'm speaking generally, I know. You'd have to try various things with your data to see what works.

There's the essential difference between the two titles. Excel is up-and-running almost immediately, but has inflexibily, while Access can be made to accommodate many ends, yet requires a great deal more planning and organizing.

My advice is to start with a new Access database and include, there, the several tables -- derived from your Excel worksheets, but with many fewer rows to keep the initial work simplified. Try querying to find significant matches as you have indicated. When brought onto a form, I expect "Continuous" will be best. An alternative to your Paradym of using formatting, perhaps show the compare result in a separate TextBox also displayed on the Form.

For example "PN xxxx5123" and "This matches an inventory item".

These data, once fully queried, could be displayed on the Form grouped by the criteria.

This is the sort of project that will help you teach yourself how to use Access. Nothing comes "right out of the box."

You can have multiple conditional formats for the same field.  See picture below.

Oh, that's right. I had forgotten. I always sort of avoid conditional formatting because I read it uses a lot of processing real estate and tends to slow down performance. At least in my early days, everything was done to avoid the need for expensive RAM and storage capacity and the chips of the day were easily slowed to a crawl by any extraneous data. I wonder if I should still be so paranoid given the vast improvement in capacity and cost of equipment. I have been using some such formatting, but my tables are relatively small. It might be worth it for this fellow to try an all conditional-formatting solution?

Thanks a lot learned lots from these tips. Before coming here i researched a lot on this topic and i found a method called the find first, do while loop function, but i dont know any vba. And i could not find anything related to this function neither on youtube or anywhere else. I asked bing chat and google bard as well. they suggested this find first method but i dont know how to create it . Sir Richard rost once said in a video to tell him if i dont find anything online related to access. Can i request a video for this find First function? It looks kinda like this:
    Sub FindOrgName()
    
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        
        'Get the database and Recordset
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("tblCustomers")
    
        'Search for the first matching record  
        rst.FindFirst "[OrgName] LIKE '*parts*'"
        
        'Check the result
        If rst.NoMatch Then
            MsgBox "Record not found."
            GotTo Cleanup
        Else
            Do While Not rst.NoMatch
                MsgBox "Customer name: " & rst!CustName
                rst.FindNext "[OrgName] LIKE '*parts*'"
            Loop
    
            'Search for the next matching record
            rst.FindNext "[OrgName] LIKE '*parts*'"
        End If
      
        Cleanup:
            rst.Close
            Set rst = Nothing
            Set dbs = Nothing
    
    End Sub

Try MS's https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/recordset-findfirst-method-dao

Do you know how RecordSet lets you use your data tables in code? FindFirst is used in conjuction with "If Not NoMatch." There's a lot to cover, but it makes sense. Google around for DAO Recordsets in MS Access. Rich has a couple of TechHelp videos on YouTube (https://www.youtube.com/watch?v=qHLQTRhU7BE, and others). He's always worth the time -- and it's free. Lots of other qualified presenters there as well.