my macro is opening all the xls files from a folder and then formating them in order to keep only row which entries (access to a room) is after 22h (NihgtLimit) or before 07h (MorningLimit)
its currently working but we would like to put the morninglimit and nightlimit as a cell value on a sheet called : Param (in activeworkbook).
nightlimit in cell B2
morninglimit in cell B3
Currently the limit are in the code and it would be helpful to be able to change these values within the param sheet instead of the Code.
the code above is called in my master code but this is the part in which we are tagging the row that appears to be "suspicious"
morning limit is : TimeValue("07:00:00")
night limit is : TimeValue("22:00:00")
here my code :
Sub SuspectEntries() Dim date1 As Range Dim c As Integer Dim i As Integer c = ActiveSheet.Cells.SpecialCells(xlLastCell).Row 'For i = 2 To c 'If Weekday(Range("D" & i).Value, vbMonday) < 6 Then ' 'Else 'Range("D" & i).Offset(0, -1).Value = "Accessed during the WeekEnd" 'End If 'Next i Dim datecell As Range Dim NTime As Date Dim MTime As Date Set datecell = Range("B1") 'Does the entry occured before 07:00 AM For Each datecell In Range(Cells(2, "B"), Cells(220, "B")) If datecell.Value <> "" Then If datecell.Value > TimeValue("00:00:00") And datecell.Value < TimeValue("07:00:00") Then datecell.Offset(i, 1) = "Before 7 AM" End If End If Next datecell 'does the entry has occured after 10 PM For Each datecell In Range(Cells(2, "B"), Cells(220, "B")) If datecell.Value <> "" Then If datecell.Value > TimeValue("22:00:00") Then datecell.Offset(i, 1) = "After 22 PM" End If End If Next datecell On Error Resume Next Columns("C").SpecialCells(xlBlanks).EntireRow.delete Columns("A:E").EntireColumn.AutoFit Range("A1") = "Name" Range("D1") = "Date" End Sub