range vba

[jtodd]

HI
I have the folowing code that creates a log of changes made ( i have a linked table so cannot use track changes)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveSheet.Name <> "Log" Then
Application.EnableEvents = False
Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & " - " & Target.Address(0, 0)
Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Target.Value
Application.EnableEvents = True
End If
End Sub

This works fine but the problem I only realy need to log changes to a specific range (eg B71-O79 and B84- O90)
How could i change the code to reflect this

 

[Norie]

Try this.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Sh.Name <> "Log" Then
    
        If Intersect(Target, Sh.Range("B71:O79,B84:O9")) Is Nothing Then Exit Sub
        
        Application.EnableEvents = False
        Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Sh.Name & " - " & Target.Address(0, 0)
        Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Target.Value
        Application.EnableEvents = True
        
    End If
    
End Sub

 

[DanteAmor]

Another approach, in this way you can log the modification of several cells at the same time.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Dim rng As Range, c As Range
 
  If ActiveSheet.Name <> "Log" Then
    Set rng = Intersect(Target, Range("B71:O79, B84:O90"))
    If Not rng Is Nothing Then
   
      For Each c In rng
        Application.EnableEvents = False
        Sheets("Log").Range("A" & Rows.Count).End(3)(2, 1).Value = Sh.Name & " - " & c.Address(0, 0)
        Sheets("Log").Range("A" & Rows.Count).End(3)(1, 2).Value = c.Value
        Application.EnableEvents = True
      Next
     
    End If
  End If
End Sub