Conditional Formatting Based on Time Stamp

Attached is a spreadsheet set up to record barcode scans and assign time stamps for inventory checkout.

A badge number is scanned or input in column A, the timestamp automatically appears in Column B, and then the PPE equipment barcode is scanned for column C.  And then the cursor advances to the next row.

I would like some help in modifying so that when a particular PPE equipment value is entered in column C, it will check if the same value was already assigned to the column A badge value within a certain time period.

For instance, the value in C might be "FREEZER".  From the first time a particular badge value in A is tied to that value in C, that entry should not repeat for a year.  If the entry falls within less than a year from the timestamp of the first badge-equipment combo, then it should be highlighted in bold and red.

So, if a record was Badge 1, Freezer on 8/28...and then Badge 1 tried to be associated with Freezer again on 10/10, the entry would come back in bold red.

Possible values for C and their time limits before repeat follow:

FREEZER - 1 YEAR
THAW - 1 YEAR
LABEL - 6 MONTHS
QC - 6 MONTHS
5TRASH - 12 HOURS
10TRASH - 12 HOURS
BOOTS - 6 MONTHS
SAFEGLASSES - 3 MONTHS
SAFEVEST - 3 MONTHS
BUMPCAP - 6 MONTHS
FREEZERGLOVES - 1 WEEK
RUBBERGLOVES - 1 WEEK
SANITATION - 6 MONTHS
RETORT - 6 MONTHS
PACKING - 6 MONTHS

Can someone help?
mrherndonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mrherndonAuthor Commented:
0
Glenn RayExcel VBA DeveloperCommented:
Any chance you could show some sample data?  I'd like to see what the actual timestamps and PPE strings look like.  Just a few rows (<20) will do.

Thanks,
-Glenn
0
Rgonzo1971Commented:
Hi,

I've created a user defined function to calculate the time difference

Function fDateDiff(date1, Interval As String, NumberOfTimes As Integer)
    Select Case Interval
        Case "yyyy"
            Res = WorksheetFunction.EDate(date1, 12 * NumberOfTimes)
        Case "m"
            Res = WorksheetFunction.EDate(date1, NumberOfTimes)
        Case "ww"
            Res = date1 + (NumberOfTimes * 7)
        Case "d"
            Res = date1 + NumberOfTimes
        Case "h"
            Res = date1 + NumberOfTimes / 24
        Case Else
            Res = CVErr(xlErrValue)
    End Select
    fDateDiff = Res
End Function

Open in new window


Created a array constant as named range DataTable

={"FREEZER",1,"yyyy";"THAW",1,"yyyy";"LABEL",6,"m";"QC",6,"m";"5TRASH",12,"h";"10TRASH",12,"h";"BOOTS",6,"m";"SAFEGLASSES",3,"m";"SAFEVEST",3,"m";"BUMPCAP",6,"m";"FREEZERGLOVES",1,"ww";"RUBBERGLOVES",1,"ww";"SANITATION",6,"m";"RETORT",6,"m";"PACKING",6,"m"}

and used them in the conditional fomatting

please replace the CF formula in C2 with the one in D2

‘=IF(COUNTIF($C$2:$C2,C2)>1,IF(fDateDiff(LARGE((--(($C$2:$C2)=C2)*$B$2:$B2),2),VLOOKUP(C2,DataTable,3),VLOOKUP(C2,DataTable,2))>MAX(--(($C$2:$C2)=C2)*$B$2:$B2),1,0),0)


The formula looks up the data in DataTable to know which time difference to calculate it is processed by the FunctionfDateDiff

Regards
BagRoomV1.xls
0
mrherndonAuthor Commented:
This looks fantastic, Rgonzo!  The only issue I have with it is that after a CF highlight, the Private Sub in Sheet 1 I have no longer works.  

After the highlight, and for every scan thereafter, the focus does not return to the first cell of the next row but instead goes down one row, say from D3 to D4.  

Can we have both?  A highlighted CF but then a return to the first cell of the next row as in Private Sub "Worksheet_Change"?

Thanks again for the help.
0
Rgonzo1971Commented:
You could add this code to your sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count = 1 Then
    Application.EnableEvents = False
    If Target.Column = 3 And Target.Row - 1 = Range("C" & Rows.Count).End(xlUp).Row Then
        Target.Offset(0, -2).Select
    End If
    Application.EnableEvents = True
    End If
End Sub
BagRoomV2.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.