How to Log Changes on Sheets!

[Paul_Hossler]

Showing different techniques

Some techniques can more easily be expanded when requirements change, or bugs need to be squashed

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rLogCell As Range
    Dim wsLog As Worksheet
    
    Select Case Target.Cells(1, 1).Address
        Case "$B$4"
            Set wsLog = Worksheets("Log")
        Case "$B$6"
            Set wsLog = Worksheets("Log2")
        Case Else
            Exit Sub
    End Select


    If Target.Cells(1, 1).Value <= 0# Then Exit Sub


    With wsLog
        Set rLogCell = .Cells(.Rows.Count, 1).End(xlUp)
        
        If Len(rLogCell.Value) > 0 Then Set rLogCell = rLogCell.Offset(1, 0)
    End With
    
    rLogCell.Value = Now


End Sub