Clear cell after Worksheet_Change Analysis

[debbert1967]

Hello all. This is my first post and I hate to be asking for help on my first post. I've found bits and pieces of info that I'm looking for but I don't know how to put it all together to make it work.

I am using Excel 2007.

What I am trying to accomplish is as follows:

Cell A1 will be used to scan barcode data into (this data is a serial number scanned off of a box using a barcode). The barcode scanner utilizes "Enter" and a line feed after the scan is complete, as I need to use this configuration for other aspects of my job.

When the data is entered into cell A1, it compares itself to data located in column "f," which are known serial numbers that I should have on-hand. It, then color codes the background of the cell in column "f" where the appropriate serial number is located.

Now what I need to happen, and I can't figure out, is to have the cursor go back to A1, clear the existing data, then wait for the new barcode scan data to arrive in order to process the new serial number information, compare it, and highlight it in column "f".

This will repeat until I have run out of serial numbers to scan. If all works well, after scanning all of my boxes, all of the entries in column F should be highlighted.

I am using this for inventory purposes to compare known data to scanned data.

The following code only gets me so far as comparing and highlighting the data and keeping my cursor in cell A1. What I can't figure out is how to clear the data in A1 in order to accept the new data being scanned in next. Any help would be most appreciated. Thank you in advance.


Private Sub Worksheet_Change(ByVal Target As Range)
Sheets(1).Select
ActiveSheet.Range("A1").Select
If Target.Address = "$A$1" Then
With Columns("F")
Set c = .Find(Target)
If Not c Is Nothing Then
Range(c.Address).Interior.ColorIndex = 6
Else
MsgBox "Value Not Found"
End If
End With
End If
End Sub

[macropod]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c
If Target.Address = "$A$1" Then
With Columns("F")
Set c = .Find(Target)
If Not c Is Nothing Then
Range(c.Address).Interior.ColorIndex = 6
Else
MsgBox "Value Not Found"
End If
End With
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End Sub

[debbert1967]

Thank you, Paul.

The code that you provided does clear the contents of cell A1 but it also advances the cursor to A2, A3, A4, etc... respectively. I always need the cursor to go back to A1, clear the contents of A1, then process a scan and routine the go back to A1, clear A1, and repeat.

[macropod]

It's not the code that's causing that; it's your Excel setup. That said, you could insert:
Target.Select
after:
Target.ClearContents

[debbert1967]

Thank you, Paul! That is exactly what I needed. It's working just I had imagined it, and I really appreciate your quick help with this. It really means a lot!