Track and record changes in a column

U

UHsoccer

Well-known Member
Joined
Apr 3, 2002
Messages
1,023
I am tracking any changes made in several colums using the following logic

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
 
    Dim rngMonitor As Range
    
    Set rngMonitor = Range("A4:A1000")  '  track first name
    If Not Intersect(Target, rngMonitor) Is Nothing _
        And Target.Address <> rngChange.Address Then Target.Offset(0, 18).Value = 1
        
    Set rngMonitor = Range("B4:B1000")  '  track last name
    If Not Intersect(Target, rngMonitor) Is Nothing _
        And Target.Address <> rngChange.Address Then Target.Offset(0, 17).Value = 1

end sub

It tracks cell changes in column A and B and places a "1" in column S

It works fine except when I insert a line or copy/insert a line it responds with

"Application-defined or Object-defined error" it highlights "Target.Offset(0, 18).Value = 1"

My guess is that since I do not click in a cell, the "Target" is not defined

Any solution would be helpfull, including simplifying the code since I actually track many non-adjacent columns
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
I would change your macro to something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column > 2 Then Exit Sub

Range("S" & Target.Row).Value = 1

End Sub


HTH,
 
Upvote 0
That is a cool solution. I included it and can separate cell changes from row activity.

If I add/copy a SINGLE row it inserts correctly, however:

1) If I delete a single row it places a "1" in the row that takes its place.

2) If I delete multiple rows (say rows 10-20), it puts a "1" in the new row 10

3) If I copy multiple rows, it places a single "1" in the first one only

Is it reasonable to handle this or would it get to complicated?
 
Upvote 0
You can also just add a comment
Code: 
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column > 2 Then Exit Sub
    Target.ClearComments
    Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName")
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column > 2 Then Exit Sub
    preValue = Target.Value
End Sub

lenze
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Autofilter and Worksheet_Change
Replies
3
Views
470
Joe4
Joe4
Agnarr
  • Question
VBA code mix
Replies
1
Views
371
Joe4
Joe4
Agnarr
  • Solved
Calculation Problem VBA
Replies
1
Views
363
Joe4
Joe4
Agnarr
  • Question
Check if it is correct
Replies
1
Views
308
rlv01
R
S
  • Solved
Time Stamp when cell value is changed
2 3
Replies
23
Views
1K
Joe4
Joe4

Forum statistics

Threads
1,215,430
Messages
6,124,851
Members
449,194
Latest member
HellScout

Share this page

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
Back
Top