Selected Answer
Sunil
Given your comment that Sheet 2 is updated remotely by a thridparty plug-in (but you can't say how) I've tried to emulate a remote change by linking your Sheet2 to another .xlsx file (also attached)
The attached .xlsm file takes a different approach to yours (and removes the VLOOKUP formula in the Strategy section, pasting values instead).
Open both files and you'll see that Sheet 2 cell E5 has the formula:
='[Remote data link v0_a.xlsx]Sheet1'!E4
and cells to the right and down have similar links.
With those links in place, if you change one of the yellow cells in the file Remote data link v0_a.xlsx, it triggers a worksheet calculation event for Sheet 2 of the .xlsm which runs this macro:
Private Sub Worksheet_Calculate()
'run through cells and modify Report if value has changed
For Each Cll In Sheet2.Range("Source")
With Sheet1.Range(Cll.Address).Offset(-1, -2)
If Cll.Value <> .Value Then
.Value = Cll.Value
End If
End With
Next Cll
End Sub
This runs through the cells in Source range and changes any which aren't that value in renamed "Report sheet" (via a fixed offset).
That triggers this event macro which records a time and count of any changes to TRUE (provided they are between Start Time and End Time- you may need to chnage these if you;'re testing outside these hours):
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C4:E6")) Is Nothing Or Target.Value <> True Then Exit Sub
Debug.Print Target.Address
Dim HypTime As Date
Dim StartTime As Date, EndTime As Date
' get time period for changes
StartTime = Range("F1").Value
EndTime = Range("F2").Value
'set criteria met time
HypTime = Format(Now(), "hh:mm")
If (HypTime >= StartTime) And (HypTime <= EndTime) Then
' if True added between start and end times, add 1 to Number of Counts and record time
Cells(Target.Row, 6) = Cells(Target.Row, 6) + 1
Cells(Target.Row, 7) = HypTime
End If
End Sub
Hope this works for you (or you can modify it to suit).