Thread: data on multiple rows

Hello nesthead98,

This macro is based on your example using columns "A" and "B". The macro will start running when the workbook is opened and stop when the workbook is closed. There are 2 code portions. The main macro goes into a VBA module. The start and stop commands are placed in the Workbook event modules "Open" and "BeforeClose", respectively.

Main Macro Code

Code:

Private Sub ShowActivity()

  Dim Rng As Range
  Dim T As Variant
  Dim TimeCell As Range
  Dim Wks As Worksheet
  
    Set Wks = Worksheets("Sheet1")
    Set Rng = Wks.Range("A1", Wks.Cells(Rows.Count, "A").End(xlUp))
    Rng.Offset(0, 1).Interior.ColorIndex = xlColorIndexNone
    
    T = Format(Now(), "hh:mm")
    Set TimeCell = Rng.Find(T, , xlValues, xlWhole, xlByRows, xlNext, False)
    
    If TimeCell Is Nothing Then Exit Sub
    
    Set Rng = TimeCell.Offset(0, 1)
    Rng.MergeArea.Interior.ColorIndex = 4  'Green
    
    Application.OnTime Now + TimeValue("00:01:00"), "ShowActivity"
    
End Sub

Sub TrackActivity(Enabled As Boolean)

  If Enabled Then
     Application.OnTime Now, "ShowActivity"
  Else
     On Error Resume Next
     Application.OnTime EarliestTime:=Now, Procedure:="ShowActivity", Schedule:=False
  End If

End Sub

Workbook Events Code

Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  TrackActivity = False
End Sub

Private Sub Workbook_Open()
  TrackActivity True
End Sub