Move scanned values from one cell to another in a range as they are scanned in excel using VBA

I have a spreadsheet at work that looks like this:

There are order numbers in columns with headers that signify the process that that order is at on the production floor. These orders are scanned from a barcode on the order sheet into a cell under whatever process it is at. I would like to scan these orders from cell to cell as they are scanned. For example, there is an order number C8VLZ70010000 in cell "D4" under the "GTOZ 741" column, if that order moves from the GTOZ 741 to any other process and I scan that same order number to any other cell in the sheet, I would like the old location ("D4") to be cleared when I scan to the other cell. This should seemingly move one order number around the worksheet without having any duplicates.

What I have in the meantime is an on change routine that identifies the duplicate values and changes the font color red. Then when the earlier entry is manually deleted by the user, the font turns back to black. That code looks like this: Code Example

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    Dim myDataRng As Range
    Dim cell As Range

    ' WE WILL SET THE RANGE (SECOND COLUMN).
    Set myDataRng = Range("A1:J34")

    For Each cell In myDataRng
        cell.Offset(0, 0).Font.Color = vbBlack          ' DEFAULT COLOR.

        ' LOCATE DUPLICATE VALUE(S) IN THE SPECIFIED.
        If Application.Evaluate("COUNTIF(" & myDataRng.Address & "," & cell.Address & ")") > 1 Then
            cell.Offset(0, 0).Font.Color = vbRed        ' CHANGE COLOR TO RED.
        End If
    Next cell

    Set myDataRng = Nothing
    ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Order numbers may also be in cells that have a fill color signifying something specific about the order such as its ship method and would like to move the fill color with the order if it moves. If someone could show me how to get things working with the order number scans, I could probably figure the cell color movement out myself. But if you choose to include that as well it would be a huge help! I appreciate any answers and thank you.

Solution

So, you have basically the whole thing written already. I think this will do what you need it to (I don't have time to test the code at the moment, if it doesn't work let me know and I'll fix it):

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    Dim myDataRng As Range
    Dim cell As Range

    'Define and set the order number and address that just changed
    Dim orderNumber As String, orderNumberAddress
    orderNumberAddress = Target.Address
    orderNumber = Target.Value2
    'If a cell is cleared of its contents, no need to check for blanks
    If orderNumber = "" Then
        Exit Sub
    End if

    ' WE WILL SET THE RANGE (SECOND COLUMN).
    Set myDataRng = Range("A1:J34")

    'make it so our event doesn't trigger itself
    Application.EnableEvents = False

    For Each cell In myDataRng
        'if the value of the cell is the same as the value of the cell that just changed,
        'AND the cell is NOT the cell that just changed
        If cell.Value2 = orderNumber And cell.Address <> orderNumberAddress Then
            cell.ClearContents
            'set the new order number cell's colorindex to match
            Target.Interior.ColorIndex = cell.Interior.ColorIndex
            'default fill color
            cell.Interior.ColorIndex = -4142
        End If
    Next

    'Not really necessary, as myDataRng goes out of scope upon completion of the subroutine
    'Set myDataRng = Nothing
ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

A few things, though:

A subroutine that makes changes to the spreadsheet will clear the undo stack. Therefore, if you have a user that meant to enter 'C8VLZ70010000' but instead entered 'D8VLZ70010000', and D8 was a valid order number, then it will clear the earlier D8, with no way to "undo" that. Now you'd have to have your user find where D8 was, put it back, and then reenter C8 to the correct cell.
A way to combat the above problem is to have some sort of msgbox warning the user. They could then say "ok" to proceed with the change, or "cancel" to exit the sub. Something like this:

Dim response As Long
response = MsgBox("Found " & orderNumber & " in cell " & cell.Address & ". OK to delete?", vbOKCancel, "Order Found")
If response = vbOK Then
    'do stuff
Else
    GoTo ErrHandler
End If

Finally, there are a few other small things you might want changed. One would be making myDataRng be a named range in the sheet rather than hard coding the address in your VBA. Another would be adding a feature that identifies what process an item is in, and use that to alert the user in your message box instead of its cell address.

Let me know if you have questions.

P.S. Rereading your question, maybe there wouldn't be typos if your getting the order number from a barcode scan? Also, if that's the case? How are you doing that? Teach me! :)