Automatic update of columns based on changes in target cell value

Hi and welcome to the Forum

In the revised workbook attached, the code below is associated with sheet UVA_Stop.If the value of 1 is written MANUALLY to cell 2 of that sheet first, it will write the start time to D3. Then (as you change the value from 1 to 0 and back to 1), it will complete cells D and E to suit plus record the Uptime (column G) and Downtime (column F) plus stop reason (column K). Note that I've declared a Public variable LastVal so the initial tests can ignore when the same value is written to cell C2 (e.g. it's already 1 and 1 is reported again) There's a private variable called LastRec to say with row in column D contains the last recoded (start).

Public LastVal As Long

Private Sub Worksheet_Change(ByVal Target As Range)

'Downtime tracking

Dim LastRec As Long

' Get the row number of the last record
LastRec = Range("D" & Rows.Count).End(xlUp).Row

'ignore input if cell C2 not changed or several cells are changed
If Intersect(Target, Range("C2")) Is Nothing Or Target.CountLarge > 1 Then Exit Sub
' ignore any zeroes in C2 before the first 1 is written
If Range("C2").Value = 0 And LastRec = 1 Then Exit Sub
' ignore if the same value is written to C2
If Range("C2").Value = LastVal Then Exit Sub

' for a valid change, record as LastVal
LastVal = Target.Value

'### stop the macro being re-triggered by its own actions
Application.EnableEvents = False

' act on value of C2
Select Case Target.Value
    Case 1
        Cells(LastRec + 1, "D").Value = Now
        If LastRec > 1 Then
            'write the downtime
            Cells(LastRec + 1, "F").Value = Cells(LastRec + 1, "D").Value - Cells(LastRec, "E").Value
        End If
    Case 0
        Cells(LastRec, "E").Value = Now
        'write the uptime
        Cells(LastRec, "G").Value = Cells(LastRec, "E").Value - Cells(LastRec, "D").Value
        'write reason for state 0
        Cells(LastRec, "K").Value = Cells(3, "B").Value
        Case Else
        'Do nothing
End Select


Range("D:D").EntireColumn.AutoFit
Range("E:E").EntireColumn.AutoFit
Range("K:K").EntireColumn.AutoFit

'### re-enable events
Application.EnableEvents = True

End Sub

Note that I've used a series of tests at the start oif the macro to get only a change in the value of C2. I've also added the line 

'### stop the macro being re-triggered by its own actions
Application.EnableEvents = False

for the commented reason (i.e. without it, when you write values, the Worksheet_Change event macro is triggered several times) and a line at the end to resttore it. Note that if the macro fails from  some reason, you can paste Application.EnableEvents = True into the Immediate window of VB Explorer (so the Worksheer_Change event and others are recognised).

Importantly (in my opinion) I've manually formatted cells in sheet UVA_Stop and deleted lines like:

Cells(i, "D").Value = Date & " " & Time
Cells(i, "D").NumberFormat = "dd/mm/yy hh:mm:ss"

(Using the ampersand & and the space makes the value a string (Text) -which doesn't get formatted as a number and prevents uptime and downtime being calculated by simple subtraction). That means the VBA function Now (system date and time) can be used and the assigned (numerical) values can be used in calculations.

Note however that while this macro should work as you manually alter the worksheet, it will not work if changes to C2 and B3 are triggered by an Excel spreadsheet updated via a remote link to the PLC machine.There is however another (workbook) event you might be able to use (WorkbookAfterRemoteChange).

Hope this helps.