I am using a bar code scanner to enter data into excel. I have a number of different units, between 20 and 40, that can only be used for 600 hours each before they need to be refurbished. There are always 8-10 units being used and they may be on between 50 and 150 hours per use. I am using this formula to return the time and date.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rChange As Range

ActiveSheet.Unprotect

On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("A:A"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
With rCell.Offset(0, 1)
.Value = Now
.NumberFormat = "m/d/yy, hh:mm AM/PM"
ActiveSheet.Protect
End With
Else
rCell.Offset(0, 1).Clear
End If
Next
End If

ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler



End Sub

When the bar code is scanned it enters the serial number of that particular unit in column A and, the time and date, go into column B. The bar code is scanned when the unit is turned on and again when it is turned off. I am looking for a way to find the total number of hours each unit is run. Manually to get this information I sort column A and selecting one unit. Then I subtract the newest entry from the second newest, giving me the total time of the last run. I then subtract the next two and so on until I have a list containing the times from all the runs since the last time the unit was refurbished. Summing the list gives me the total time the unit has ran. I would like a way to do this with a formula or a number of formulas so I have a cell containing the total time a unit has been used before I turn it on. That will also allow me to use conditional formatting to change the color of the cell with the total time to make it easier to see how close the units are to being refurbished again.