Dave,

Beautiful, beautiful! Thank you so much and I will heed your advise.

Bob


"robert morris" wrote:

Dave,

Column F9:F188 is perfect. Now if I go to Column G9:G188 times the static
number of 5, using the same code I get an Ambiguous error. I also need for
Range H9:H188 *10 and Range I9:I188 * 20. Must I change the name"Private
Sub Worksheet_Change" for each one? Your help is greatly appreciated.

Bob



----- Original Message -----
From: "Dave Peterson"
Newsgroups: microsoft.public.excel.misc
Sent: Thursday, November 05, 2009 8:32 AM
Subject: VBA - Can I?


Or use this.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myIntersect As Range
Dim myRngToCheck As Range
Dim myCell As Range

Set myRngToCheck = Me.Range("F9:F188")

Set myIntersect = Intersect(Target, myRngToCheck)

If myIntersect Is Nothing Then
Exit Sub
End If

For Each myCell In myIntersect.Cells
With myCell
If IsNumeric(.Value) Then
Application.EnableEvents = False
.Value = .Value * 2
Application.EnableEvents = True
End If
End With
Next myCell
End Sub


"robert morris" wrote:

Thanks for the reply but the code doesn't change the number entered in Cell
F9. If I type in "3" in F9 and "enter" the Cell F9 should show the result of
"6". It shows 3.

I forgot to say the Range should be Column F9:F188.

I am presently using an adjacent cell with a formula which works but a code
would make life easier.

Bob

"FSt1" wrote:

hi
not sure if i understand but...
try this.....
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target = [F9]
Target.Value = Target.Value * 2
End Sub

worksheet code. right click the sheet tab and from the popup, click "view
code"
Paste the above into the code window. works only on the sheet you paste it
into.

Regards
FSt1

"robert morris" wrote:

Can I use VBA code to enter a number say 1 or 2 or 3 or 4 in Col F9 and have
it multiply by a static number, say 2 and have the result display in the same
Col F9?

Bob