Macro Runs when the user changes any cell on the work sheet

[Guest]

I want to run a macro (just makes sure the end user doesn't accidentally
delete some of the formulas from a column) when the user adds/changes any
cell value. The idea is that they input the following on each line, and the
Total column multiplies the unit cost by the quantity.

Product Unit Cost £ Quantity Total


However, if they add rows the formula disappears and the calculation doesn't
work. I want to add a macro so when they add/change any of the left hand
cells (even if they have added a row) the formula will appear in the totals
column and complete the calculation.

Any ideas?

 

[papou]

Hello
Right-click on the worksheet tab, select View Code
With columns starting from A to D,
paste this sample code (with no exchaustive testing) and amend to your
needs:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range(Target.Address), Range("A:C")) _
Is Nothing Then
Dim r As Long
r = Target.Row
If Cells(r, "B").Value <> "" And _
Cells(r, "C").Value <> "" Then
Cells(r, "D").Value = Cells(r, "B") * Cells(r, "C")
End If
End If
End Sub

HTH
Cordially
Pascal

 

[Guest]

Hi
Thanks for the macro, I was wondering if there was a way of making it more
generic like if any row of column was changed the the calculation would be
done (the real data has a lot more columns and rows in reality).

Tom

 

[papou]

Tom
The sample code has no limits in rows, it just checks for the relevant
values in columns to achieve your calculation.
You can easily amend with the relevant range of columns (A to IV for
instance).
But you will need to identify both Unit Cost and Quantity columns (so it
can't be that generic).

HTH
Cordially
Pascal

 

[Guest]

OK. I wasn't sure if I could just say something like if any column was change
(A:Z) then it would trigger the calculation.

Thanks.