Multiply with Formula to the existing cell values

Hi,

I need to multiply the values of particular row with other row with formula link.

Example values of 4th row to be multiply with row 2 & 3 with formula like in A2 cell "=A2 cell value * A4"

If i change the value of A4, A2 & A3 also should change accordingly.

I have attached excel workbook with sample data.

Request you to help with VBA code.

In Worksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Not Intersect(Target, Rows(4)) Is Nothing Then
  For Each rng In Intersect(Target, Rows(4))
    If rng.Value <> 0 And IsNumeric(rng) Then
      Application.EnableEvents = False
      rng.Copy
      rng.Offset(-2, 0).Resize(2, 1).PasteSpecial operation:=xlPasteSpecialOperationMultiply
      Application.EnableEvents = True
    End If
  Next rng
  Application.CutCopyMode = False
  Target.Select
End If
End Sub

PS. note that to change it back, you need it inverted, so if you had
100
5
and entered in row 4
2

you will have
200
10
2

now to change it back to
100
5
you shall enter in row 4
0.5
or

Formula:

=1/2

of course one could add

application.screenupdationg=false

at the beginning and set it to True at the end od sub

It is in a worksheet code, not in standard module.
So you don't have to manually run it.

just open the file, enable macros, change value in one of cells in row 4 (confirm change with enter) and see how cells in row 2 and 3 changes.

This is how I understood your request:

If i change the value of A4, A2 & A3 also should change accordingly.

Sub Multiply_by_row_4()
Dim rng As Range, Target As Range, storeselection as object
set storeselection = selection
Set target = activesheet.range("B4:CB4")
'If Not Intersect(Target, Rows(4)) Is Nothing Then
'  For Each rng In Intersect(Target, Rows(4))
  For Each rng In Target
    If rng.Value <> 0 And IsNumeric(rng) Then
'      Application.EnableEvents = False
      rng.Copy
      rng.Offset(-2, 0).Resize(2, 1).PasteSpecial operation:=xlPasteSpecialOperationMultiply
'     Application.EnableEvents = True
    End If
  Next rng
  Application.CutCopyMode = False
'  Target.Select
storeselection.select
'End If
End Sub

Try the above in standard module