making cells change relative to each other? cant figure it out

[MMA]

my question/problem is kind of weird but here goes. I'm praying
someone smart and nice with some vba knowledge can help

I'm doing a food data worksheet for my nutrition class

I have five columns per food

quantity, calories. protein, carbs, fat

the foods are also in rows

What i want to do is enter the food data in for each food and
then have it so later on if i want to change a value in any of
the columns, the rest will move relative to the one

for example, if 1 oz chicken breast is 46 calories, 1 gram fat,
0 carb, 9 protein. I can change the protein to be 25 (multiplied
by 2.778) the rest will multipy their current values by 2.7778
automatically.

I talked to some people and heard vba is probably the only way
to do it and i am clueless when it comes to that. Any help with
an appropriate script for what im trying to do would be much
appreciated

thanks in advance

 

[Bob Phillips]

Private PrevVal As Double

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "B:E"
Dim i As Long

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
For i = 2 To 5
If i <> .Column Then
Me.Cells(.Row, i).Value = Me.Cells(.Row, i).Value *
..Value / PrevVal
End If
Next i
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
PrevVal = Target.Value
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)