Change Log

[Nasmin Saheed]

HI,

I wanted to know that, if I changed any field in excel in one column it should appear that what I have done the changes - here multiple changes can happen bu I want the last change report - is possible in Excel? if so plz assist me - Thanks

 

[bebo021999]

It seems worksheet_change even will be used.
what exactly the report could be? write to sheet or via messge box?
It would help a lot with image/ screenshot or XL2BB attached.

 

[Nasmin Saheed]

 

[Nasmin Saheed]

is this possible in Excel?

 

[bebo021999]

Yes its possible, will deal with it when i come back at PC.

 

[bebo021999]

Right click on sheet name, view code, then apply below code in code window

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lr&, i&, j&
Dim rng As Range, cell As Range
Dim Old()
Lr = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("B2:K" & Lr)
ReDim Old(1 To Lr, 1 To rng.Columns.Count)
Old = rng
    If Not Intersect(Target, rng) Is Nothing Then
        ' save the old value
        Application.EnableEvents = False
        Application.Undo
        Old = rng
        Application.Undo
        Application.EnableEvents = True
            For i = 2 To Lr 'assume changing range starts from row 2
                For j = 2 To rng.Columns.Count + 1 'assume changing range starts from column B
                    If Cells(i, j).Value <> Old(i - 1, j - 1) Then
                        Cells(i, 13).Value = Cells(i, j).Address(0, 0) 'write address into cell
                        Cells(i, 14).Value = Old(i - 1, j - 1) + 0 & " to " & Cells(i, j).Value ' write old and new value into cell
                    End If
                Next
            Next
    End If
End Sub