Help coloring a cell after the value has been matched in another cell.

[catigo]

Hi,

I'm looking to finish up this scan to match form, and I'm getting stuck. I am converting another project that I used for a similar task, but I need to change one portion. When the user scans a bar code into the text box ThisBID, the information will be matched with a previously populated list in column A. When the scan is placed into the matching column B cell, I would like for the value in column A to be colored green.

In my previous tool, I was doing all of this on a button press and did not need to incorporate it into the scanner section. But now, I would like to add that function into the ThisBID_KeyDown sub. is there any way this can be done?

As a side note, I commented out code that I didn't think I would need for this project, but have not removed it yet. I apologize for the sloppy presentation.

Thanks!

Private Sub ThisBID_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = 13 Then
    Dim BID As String
    Dim strFind As String
    Dim rngFind As Range
    Dim strFirstAddress As String
    
    With Range("A:A")
        Set rngFind = .Find(ThisBID.Text, LookIn:=xlValues, LookAt:=xlWhole)
        If Not rngFind Is Nothing Then
            strFirstAddress = rngFind.Address
            Do
                rngFind.Offset(0, 1).Value = ThisBID.Text
                Set rngFind = .FindNext(rngFind)
             Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
         End If
      End With
      
    strFind = ThisBID
     
    'txtplotNum.Text = WorksheetFunction.VLookup(strFind, Worksheets("Stand Counts").Range("A2", "A:D"), 4, False)
    
    LastRow = Sheets("Verify Inventory").Cells(Rows.Count, 1).End(xlUp).Row
    BID = ThisBID
    If LastBID = "" Then
        LastBID = ThisBID
        With Sheets("Verify Inventory").Range("A1").Resize(LastRow, 1)
            Set FoundBID = .Find(BID, LookIn:=xlValues)
            If FoundBID Is Nothing Then
                NotFoundError
                KeyCode = 0
                Exit Sub
            End If
            With FoundBID
                .Interior.Color = RGB(0, 255, 0)
            End With
        End With
    Else
   With Sheets("Verify Inventory").Range("A1").Resize(LastRow, 1)
            Set LastBIDRow = .Find(LastBID, LookIn:=xlValues)
            Set FoundBID = .Find(BID, LookIn:=xlValues)
            If FoundBID Is Nothing Then
                NotFoundError
                KeyCode = 0
                Exit Sub
            End If
            If LastBIDRow.Offset(1, 0) = FoundBID Then
                With FoundBID
                    .Interior.Color = RGB(0, 255, 0)
                End With
                
            Else
                'Application.Wait Now + TimeValue("00:00:01")
                'MsgBox "Not In Order"
                'KeyCode = 0
                LastBID = ThisBID
                ThisBID = vbNullString
                GoTo skip
            End If
    End With
   End If
    LastBID = ThisBID
skip:
    ThisBID = vbNullString
    cmd_Clear.SetFocus
    ThisBID.SetFocus
    ThisBID.SelStart = 0
    
End If


End Sub


Private Sub NotFoundError()
    
    Application.Wait Now + TimeValue("00:00:01")
    
    MsgBox "Record not found." _
        & vbCr & "Please check the record being scanned."
    LastBID = vbNullString
    ThisBID = vbNullString
    cmd_Clear.SetFocus
    ThisBID.SetFocus
    ThisBID.SelStart = 0
End Sub


Private Sub cmd_Enter_Click()
    Unload Me


End Sub
Private Sub cmd_Clear_Click()
ThisBID.Text = ""
ThisBID.SetFocus
End Sub

 

[pbornemeier]

Could you use conditional formatting on column A with the condition of =Ax=Bx (where X is row number) and the true condition to turn the matching cell to the desired color?

 

[catigo]

I actually was able to figure the issue out. I initially tried using conditional formatting, but with the size of some of my inventory files, that seemed to do nothing but bog down my file. Thanks for the reply!