I still don’t understand. Please give an example of what you want the worksheet to do. Change the value in B4 and tell us what you wnat to happen in F4.

I still don’t understand. Please give an example of what you want the worksheet to do. Change the value in B4 and tell us what you wnat to happen in F4.

Let’s take row 4 as example. In the workbook you posted, we have:
B4 = 1.20
D4 = 4
E4 = 9.68
F4 contains the formula =D4*E4, so its value is 4*9.68 = 38.72.
Now, the user changes the value of B4 from 1.20 to 1.30. What do you want to happen exactly? Please be as specific as you can.

That is ugly! This cannot be done without changing the formula, for the formula says ‘multiply D4 and E4’, no more and no less. The following event procedure will adjust the formula, but it will become very unwieldy over time, and there is no undo if the user makes a mistake!

Right click the sheet tab.
Select View Code.
Copy the following code into the module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
Application.EnableEvents = False
If Not Intersect(Target, Range(“B4:B18”)) Is Nothing Then
For Each oCell In Intersect(Target, Range(“B4:B18”))
oCell.Copy
oCell.Offset(0, 4).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationAdd
Next oCell
End If
Application.EnableEvents = True
Application.CutCopyMode = False
Set oCell = Nothing
End Sub

Switch back to Excel.
Save the workbook.
Change a cell in B4:B18 to test the effect – look at the corresponding cell in column F.

I don’t like this method at all, but the alternative is to use a separate column to accumulate the changes, and you didn’t want that either.

It would be much easier if the user entered the cumulative value in B4 etc. So if it is 6.80 now, and there is an increase of 1.30, the user would enter 8.10 (being the result of 6.80+1.30) in B4. The formula in F4 would be simply =D4*E4+B4, and no code would be necessary.

Why not make column B contain zeros, and simply make F = D*E+B as suggested before?

Whenever you want to make a change you alter the value in B for the relevant rows.

Why not make column B contain zeros, and simply make F = D*E+B as suggested before?

Whenever you want to make a change you alter the value in B for the relevant rows.

It would be much easier if the user entered the cumulative value in B4 etc. So if it is 6.80 now, and there is an increase of 1.30, the user would enter 8.10 (being the result of 6.80+1.30) in B4. The formula in F4 would be simply =D4*E4+B4, and no code would be necessary.

That is ugly! This cannot be done without changing the formula, for the formula says ‘multiply D4 and E4’, no more and no less. The following event procedure will adjust the formula, but it will become very unwieldy over time, and there is no undo if the user makes a mistake!

Right click the sheet tab.
Select View Code.
Copy the following code into the module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
Application.EnableEvents = False
If Not Intersect(Target, Range(“B4:B18”)) Is Nothing Then
For Each oCell In Intersect(Target, Range(“B4:B18”))
oCell.Copy
oCell.Offset(0, 4).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationAdd
Next oCell
End If
Application.EnableEvents = True
Application.CutCopyMode = False
Set oCell = Nothing
End Sub

Switch back to Excel.
Save the workbook.
Change a cell in B4:B18 to test the effect – look at the corresponding cell in column F.

I don’t like this method at all, but the alternative is to use a separate column to accumulate the changes, and you didn’t want that either.

Let’s take row 4 as example. In the workbook you posted, we have:
B4 = 1.20
D4 = 4
E4 = 9.68
F4 contains the formula =D4*E4, so its value is 4*9.68 = 38.72.
Now, the user changes the value of B4 from 1.20 to 1.30. What do you want to happen exactly? Please be as specific as you can.