• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

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?
0
mrherndon
Asked:
mrherndon
  • 2
  • 2
2 Solutions
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now