0

I have cell "B16" that displays a "1" if a particular machine is running or "0" if it is not running. These values are updated live, but I want to keep record of the timestamp of when this value changes (whether from 1 to 0 or vice versa) and create a list where the next available cells below are populated with the next status change and time stamp. The purpose of this is to be able to determine when the machine went down, how long it was down for, and when it started to run again.

Thank you, GEstrada

GEstrada
  • 1
  • Here's some resources for that: [detecting changes in the values of certain cells](https://stackoverflow.com/questions/4668410/how-do-i-get-the-old-value-of-a-changed-cell-in-excel-vba?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa) – Mikael Kajander May 29 '18 at 23:17

1 Answers1

0

This code will only execute when a change occurs in your target cell (B6).

When the value in B6 changes, it will post a time stamp (down to the second) starting at B7 on the first instance, B8 on the next instance, and so on...

Paste this code on the sheet where your target cell (B6) is. Also, you will need to change all instances of "Sheet1" to your sheet name. 

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Set KeyCells = Range("B6")

    Dim WS As Worksheet
    Set WS = ThisWorkbook.Sheets("Sheet1")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
        Is Nothing Then
        Dim LRow As Integer
        LRow = WS.Range("B" & WS.Rows.Count).End(xlUp).Row
        WS.Range("B" & LRow + 1).Value = Now()
    End If
End Sub
urdearboy
  • 14,439
  • 5
  • 28
  • 58
  • I'm not sure if the change event would be initiated if it was from an external link. If so, this answer is better than mine. I wrote mine assuming the event would not be triggered by an external reference updating. – pgSystemTester May 30 '18 at 03:54