title: VBA Excel - Modify data on cells through VBA code
body: <p>I've written some VBA code to the following:</p>
  
  <ol>
  <li>Let's say that I have an spreadsheet with this columns</li>
  </ol>
  
  <p>[Cost1] [Cost2] [Cost3] [TotalCost] [Margin%] [Margin$] [Price]</p>
  
  <ol>
  <li>If the user modifies the costs, the total cost changes and the Margin$ and Price because they depend on the cost and the Margin%</li>
  <li>If the user modifies the Price, the cost don't change but the Margin% and the Margin$ do change, because they depend on the new price.</li>
  </ol>
  
  <p>I was not able to add protected formulas to the Price column because the user may want to change that value, thus the formula would be erased. So I decided to code VBA which works perfectly calculation wise. However, I've lost some of the most valued features of excel: e.g. If a want to copy the value of one price to several other rows, it just triggers the recalculation for the firs row where it is copied but not for the others. I'v also lost the ability of UNDO after exiting the cell.</p>
  
  <p>To detect that a cell was modified I'm using the following:</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Column = Range("Price").Column)                 
      Call calcMargins(Target.Row)
    End If
  
    If (Target.Column = Range("Cost1").Column) or _
    If (Target.Column = Range("Cost2").Column) or _
    If (Target.Column = Range("Cost3").Column) or
      Call calcMargins(Target.Row)
      Call calcPrice(Target.Row)
    End If
  </code></pre>