Solved How to search and highlight cell using a barcode scanner.

January 25, 2019 at 05:25:45
Specs: Windows 10
I am looking to scan a barcode in cell A1 and excel will find and highlight the barcode in column 2. I also want to to highlight the cells to the right of the barcode in the same row but columns 3-6. In addition, in column 7 it should insert the text "P" and then put a time stamp in column 8. Is this even possible? I am so lost.

message edited by TJB96


See More: How to search and highlight cell using a barcode scanner.

Reply ↓  Report •

✔ Best Answer
January 25, 2019 at 11:48:33
Note that it will color first empty row also.
You gotta find LastRow and do for the range until that and not further.

Dunno if the code below is best way to modify the code DerbyDad posted above, but it works. When you empty cell A1 it won't give an error, nor it will color first empty row after the table. The rest is the same, I just added the condition. Credits go to DerbyDad, no condition would exist without him :)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
    LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

If Target.Address = "$A$1" Then
    If Target <> "" Then
    With Range("B1:B" & LastRow)
        Set bc = .Find(Target, lookat:=xlWhole)
            If Not bc Is Nothing Then
                Range(Cells(bc.Row, 2), Cells(bc.Row, 5)).Interior.ColorIndex = 6
                Cells(bc.Row, 6) = "P"
                Cells(bc.Row, 7) = Now
            Else: MsgBox "Barcode Not Found"
            End If
    End With
    End If
End If
End Sub

message edited by Mrrrr



#1
January 25, 2019 at 06:48:25
I'm lost too. What do you mean by "scan"?

Are you talking about a hardware/software system where you use a handheld scanner/smartphone app to physically scan the barcode and then pass that information back to Excel to process?

If not, what exactly is your desired process for "scanning" the barcode in A1?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
January 25, 2019 at 09:36:28
So forget the barcode scanner part. I have that all figured out. What I am looking to do is take attendance for a large class using a student id. The class is over 250 students and there is not enough time to take attendance by hand. You wrote a code for a previous post that works great but I want to add to it. (see link below) I am looking to use Microsoft Excel 2016 and when I enter the student ID into cell A1 and press enter, It will find the student id in column B and highlight the student Id in column B plus the students grade in column C, Name in D and E, and then put a "P" for present in column F and insert a timestamp in column G. The current excel sheet already has the data for ID, grade level, and name in columns B through E.

LINK: https://www.computing.net/answers/o...


Reply ↓  Report •

#3
January 25, 2019 at 10:38:25
Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$A$1" Then
  With Columns(2)
   Set bc = .Find(Target, lookat:=xlWhole)
    If Not bc Is Nothing Then
     Range(Cells(bc.Row, 2), Cells(bc.Row, 5)).Interior.ColorIndex = 6
     Cells(bc.Row, 6) = "P"
     Cells(bc.Row, 7) = Now
    Else: MsgBox "Barcode Not Found"
    End If
  End With
 End If
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
January 25, 2019 at 11:48:33
✔ Best Answer
Note that it will color first empty row also.
You gotta find LastRow and do for the range until that and not further.

Dunno if the code below is best way to modify the code DerbyDad posted above, but it works. When you empty cell A1 it won't give an error, nor it will color first empty row after the table. The rest is the same, I just added the condition. Credits go to DerbyDad, no condition would exist without him :)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
    LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

If Target.Address = "$A$1" Then
    If Target <> "" Then
    With Range("B1:B" & LastRow)
        Set bc = .Find(Target, lookat:=xlWhole)
            If Not bc Is Nothing Then
                Range(Cells(bc.Row, 2), Cells(bc.Row, 5)).Interior.ColorIndex = 6
                Cells(bc.Row, 6) = "P"
                Cells(bc.Row, 7) = Now
            Else: MsgBox "Barcode Not Found"
            End If
    End With
    End If
End If
End Sub

message edited by Mrrrr


Reply ↓  Report •

#5
January 25, 2019 at 12:34:35
Mrrrr....

TJB96 said "when I enter the student ID into cell A1"

The only time my code will highlight the next empty row is if the user deletes the value in A1.

I guess it's OK to account for that situation, but if the process is to enter a value (or scan a barcode) I don't see a circumstance when the value in A1 will be deleted.

Oh, wait...if there are users involved, I guess anything is possible. ;-)

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#6
January 25, 2019 at 12:41:12
Oh, my bad :) Sorry

Reply ↓  Report •

#7
January 25, 2019 at 13:04:49
Don't be sorry. It's good point and it certainly can't hurt to account for that situation.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Ask Question