I have a very complex Excel file that should have been replaced with a database a looong time ago, however it is not possible at the time. There is a need to implement a way to record when a change was made to a certain columns and record the date when the change was made.
I wrote the following script:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim VRange As Range, cell As Range
Dim Vrange2 As Range, cell2 As Range
Dim Vrange3 As Range, Cell3 As Range
Dim Vrange4 As Range, Cell4 As Range
Dim Vrange5 As Range, Cell5 As Range
Dim Vrange6 As Range, Cell6 As Range
Application.Calculation = xlCalculationManual
Set VRange = Range("J5:J7000")
For Each cell In Target
If Union(cell, VRange).Address = VRange.Address Then
cell.Offset(, 8) = "TS on " & Date
End If
Next cell
Set Vrange2 = Range("K5:K7000")
For Each cell2 In Target
If Union(cell2, Vrange2).Address = Vrange2.Address Then
cell2.Offset(, 7) = "GS on " & Date
End If
Next cell2
Set Vrange3 = Range("M5:M7000")
For Each Cell3 In Target
If Union(Cell3, Vrange3).Address = Vrange3.Address Then
Cell3.Offset(, 5) = "P on " & Date
End If
Next Cell3
Set Vrange4 = Range("O5:O7000")
For Each Cell4 In Target
If Union(Cell4, Vrange4).Address = Vrange4.Address Then
Cell4.Offset(, 3) = "GD on " & Date
End If
Next Cell4
Set Vrange5 = Range("P5:P7000")
For Each Cell5 In Target
If Union(Cell5, Vrange5).Address = Vrange5.Address Then
Cell5.Offset(, 2) = "TD on " & Date
End If
Next Cell5
Application.Calculation = xlCalculationAutomatic 'doesnt do anything
End Sub
It works as a clockwork, however due to the nature of the spreadsheet it takes forever to recalculate if more than one cells were changed (since it recalculates after the cell is changed, then runs the macro, which changes another cell, so it recalculates again and it repeats the process for every single cell that was changed) The calculation time of 1,5 seconds becomes unbearable when you change over 50 cells.
Inserting Application.Calculation=xlCalculationManual didnt help, since I need it to be automatic after the macro finished running, so I have to put Application.Calculation=xlCalculationAutomatic in the end.
When dealing with Worksheet_Change events together with Calculation mode changes, always use this:
Application.EnableEvents = False
When all cells are changed, use the opposite to enable it again:
Application.EnableEvents = True