range vba

jtodd

Board Regular
Joined
Aug 4, 2014
Messages
155
HI
I have the folowing code that creates a log of changes made ( i have a linked table so cannot use track changes)

VBA Code:
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
Try this.
VBA Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Another approach, in this way you can log the modification of several cells at the same time.

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,979
Messages
5,599,156
Members
414,294
Latest member
shariflotfi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top