Insert this code into your worksheet. Right click on worksheet tab, view
code and copy/paste code below.
It assumes data is in columns B to F (2 to 6) - adjust as required.
Option Explicit
Dim OldValue As Variant
Dim NewValue As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
OldValue = Target(1).Formula
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim row As Long, col As Integer, factor As Double
On error goto wsexit:
Application.EnableEvents = False
If Target(1).Formula < OldValue Then
MsgBox "Used to be " & CStr(OldValue) <=== remove when tested
row = Target(1).row
factor = Target(1).Value / OldValue
For col = 2 To 6
Cells(row, col) = Cells(row, col) * factor
Next
End If
wsexit:
Application.EnableEvents = True
End Sub
HTH
"Nick Hodge" wrote:
If you only need to multiply a column by a value, enter a value in a spare
cell, copy it and select the column data you need to change and then go
editpaste specialvalues+multiply
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS
"MMA" wrote in message
ng.com...
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
----------------------------------------------
Posted with NewsLeecher v2.3 Final
* Binary Usenet Leeching Made Easy
* http://www.newsleecher.com/?usenet
----------------------------------------------