title: Worksheet_Change Sub slow autocalculation
body: <p>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.</p>
  <p>I wrote the following script:</p>
  <pre><code>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(&quot;J5:J7000&quot;)
  For Each cell In Target
  If Union(cell, VRange).Address = VRange.Address Then
  cell.Offset(, 8) = &quot;TS on &quot; &amp; Date
  End If
  Next cell
  
  Set Vrange2 = Range(&quot;K5:K7000&quot;)
  For Each cell2 In Target
  If Union(cell2, Vrange2).Address = Vrange2.Address Then
  cell2.Offset(, 7) = &quot;GS on &quot; &amp; Date
  End If
  Next cell2
  
  Set Vrange3 = Range(&quot;M5:M7000&quot;)
  For Each Cell3 In Target
  If Union(Cell3, Vrange3).Address = Vrange3.Address Then
  Cell3.Offset(, 5) = &quot;P on &quot; &amp; Date
  End If
  Next Cell3
  
  Set Vrange4 = Range(&quot;O5:O7000&quot;)
  For Each Cell4 In Target
  If Union(Cell4, Vrange4).Address = Vrange4.Address Then
  Cell4.Offset(, 3) = &quot;GD on &quot; &amp; Date
  End If
  Next Cell4
  
  Set Vrange5 = Range(&quot;P5:P7000&quot;)
  For Each Cell5 In Target
  If Union(Cell5, Vrange5).Address = Vrange5.Address Then
  Cell5.Offset(, 2) = &quot;TD on &quot; &amp; Date
  End If
  Next Cell5
  
  Application.Calculation = xlCalculationAutomatic 'doesnt do anything
  End Sub
  </code></pre>
  <p>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.</p>
  <p>Inserting <code>Application.Calculation=xlCalculationManual</code> didnt help, since I need it to be automatic after the macro finished running, so I have to put <code>Application.Calculation=xlCalculationAutomatic</code> in the end.</p>