Hi,

I would like to have Excel multipling a number that I enter in a cel
by a factor 4 or 6 depending on the value of another cell (in the sam
row).
Meaning:
if I enter 1000 in G4 and hit Enter (or move to another cell) a cod
should run and check $B4 and if there is a "1000ML" it should multipl
by 6 and if there is a "1500ML" it should multiply by 4.

Any idea?

Tank

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

I already tried the

Worksheet_SelectionChange
and
Worksheet_Change

functions but this ends eighter in a loop (calculate) or it changes m
cells always when I move from one to the other...

Any ideas??

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Goepf,

To use the WorksheetChange function without looping, try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Static lockout As Boolean

If lockout Then Exit Sub
lockout = True

'Make your calculations here

lockout = False

End Sub


The 'Static' declaration means that the program 'remembers' the value of the
variable [lockout] between runs of the routine. Making a calculation in this
routine, and writing it to the sheet WILL cause another Change event, as you
have found, which needs to be locked out.

You can also use another technique which will disable the change event.

Application.EnableEvents=False
' Make Calculations here
Application.EnableEvents=True

HTH,
Alex J


"goepf" wrote in message
...

I already tried the

Worksheet_SelectionChange
and
Worksheet_Change

functions but this ends eighter in a loop (calculate) or it changes my
cells always when I move from one to the other...

Any ideas???


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

Thanks Alex,

Now an additional question:

Is it possible to apply these code only for a section of the Worksheet?
Or do I have to test the "target" location before executing the
calculation?

Thanks so far!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

geopf,

You will need to test Target to see if it is the right range to make a
change.

Opions would be (among others):

If Target.column = 3 Then .....
or
If Target.address = "$A$12" Then ...
or
(assuming you have a named range "MultRange" on the sheet)

Dim Isect as Range
Dim nmRng as Range
Set NamedRng =Sheets("Sheet1").Range("MultRange")

Set Isect = Intersect(Target, NamedRng)
If Not Isect Is Nothing then
'Do your calculation on the target cell(s) here
End if


Hope this helps.
Alex J

"goepf" wrote in message
...

Thanks Alex,

Now an additional question:

Is it possible to apply these code only for a section of the Worksheet?
Or do I have to test the "target" location before executing the
calculation?

Thanks so far!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements