Excel change history

[mr2bart]

Hi all,
I am trying to find a simple way to highlight changes on excel files.
I found in Microsoft VBA help this points:
like
activeworkbook.keephistorychange
activeworkbook.changehistoryduration
activeworkbook.highlightchangesoptions

but nothing works, even changehistoryduration is kept to 0 when I try to change to 7.

Does these features are enable for excel?

Thanks!

 

[Michael M]

Have you had a look at using the Track Changes feature ??
To turn on try

Turn on Excel Track Changes feature
On the Review tab, in the Changes group,
click the Track Changes button, and then select Highlight Changes....
In the Highlight Changes dialog box, do the following: Check the Track changes while editing. ...

 

[Logit]

.
Paste this in ThisWorkbook module :

Option Explicit


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


Dim Oldvalue As String
Dim Newvalue As String


Application.EnableEvents = True


Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value


    If Oldvalue = "" Then
        Target.Value = Newvalue
    Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & ", " & Newvalue
        Else:
            Target.Value = Oldvalue
        End If
            
    End If
Application.EnableEvents = True


'Change font color to yellow
Target.Interior.Color = vbYellow


ActiveCell.Offset(0, 1).Select    'change cusor position after yellow cell


Application.ScreenUpdating = True
End Sub

Insert a module and paste this (clears out the highlighted cells that were colored yellow due to change) :

Option Explicit
Sub UnColorCells()


     'Macro purpose:  To uncolor all yellow cells
          
    Dim cl As Range, ws As Worksheet, lColor As Long
     
     'Set the cell color that you want to protect
    lColor = 6
    
    'Cycle through each worksheet in the workbook
    
    For Each ws In ActiveWorkbook.Worksheets
        For Each cl In ws.UsedRange
             
            If cl.Interior.ColorIndex = lColor Then
                cl.Interior.ColorIndex = 0
            End If
        Next cl
    Next ws
     
End Sub

Also for consideration, if you want to track the changes that others make and you want to keep it hidden, there are other macros to accomplish it.

 

[mr2bart]

Hi both,
Thanks very much for your prompt feedback.
So for the excel integrated function, yes, I forgot to check the REVIEW menus. I need study further how to use it, I afraid it will conflict with my own other macros.
For the coding solution, yes also, I did already very similar point to highlight by color, and I improved it thanks to your more optimized/expert coding way.
THANKS!!!

 

[Logit]

.
You are welcome.