Excel Macro help - If target cell in range of cells.

[ullrichr]

Ok, a novice...

Here is a script I'm working on. It is for a barcode scanner. When they scan a bar code it places it in the Excel selected and enter date and time info in col B, C, and D
It work for the most part but I'm trying to have it check to see if the barcode value placed in the cell is in col A of another worksheet ("Student Roster") to make sure it is a valid entry. If it's not valid clear the cell before it enters the other data and give an error beep. If it is valid play a successful "beep" and enter the date and time info.

Any suggestion would be great.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range
Dim rCell As Range
Dim tCell As Range
Set rInt = Intersect(Target, Range("A:A"))
If Not rInt Is Nothing Then
    'how to?
    If Target.Value = Range("A:A") Then
        For Each rCell In rInt
            Set tCell = rCell.Offset(0, 1)
            If IsEmpty(tCell) Then
                tCell = Now
                tCell.NumberFormat = "mmm dd, yyyy hh:mm:ss AM/PM"
            End If
            Set tCell = rCell.Offset(0, 2)
            If IsEmpty(tCell) Then
                tCell = Int(Now)
                tCell.NumberFormat = "dddd mmm dd, yyyy"
            End If
            Set tCell = rCell.Offset(0, 3)
            If IsEmpty(tCell) Then
                'how to pull just the time
                tCell.NumberFormat = "hh:mm:ss AM/PM"
            End If
        Next
        'rest of the IF
        Else
        Beep
    End If
End If
End Sub

[p45cal]

try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range
Dim rCell As Range
Dim tCell As Range
Dim myTime As Date

Set rInt = Intersect(Target, Range("A:A"))
If Not rInt Is Nothing Then
  'just in case you're doing this at midnight
  myNow = Now
  myDate = Int(myNow)
  myTime = myNow - myDate

  On Error GoTo errHandler    'only enable when finished debugging. (Speeds up)
  Application.EnableEvents = False    'only enable when finished debugging.
  For Each rCell In rInt
    If Not IsError(Application.Match(rCell.Value, Sheets("Student Roster").Range("A:A"), 0)) Then
      Set tCell = rCell.Offset(0, 1)
      If IsEmpty(tCell) Then
        tCell = myNow
        tCell.NumberFormat = "mmm dd, yyyy hh:mm:ss AM/PM"
      End If
            
      Set tCell = rCell.Offset(0, 2)
      If IsEmpty(tCell) Then
        tCell = myDate
        tCell.NumberFormat = "dddd mmm dd, yyyy"
      End If
            
      Set tCell = rCell.Offset(0, 3)
      If IsEmpty(tCell) Then
        tCell = myTime
        tCell.NumberFormat = "hh:mm:ss AM/PM"
      End If
    Else    'not a match in student roster:
      Application.EnableEvents = False    'must be present here if not elsewhere to prevent infinite looping.
      rCell.ClearContents
      Beep
    End If
  Next
'Else    'this Else line not needed, not a change in column A
End If
errHandler:
Application.EnableEvents = True
End Sub