I think that this does it

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False

With Target
If Right$(.Value2, 1) = "-" Then
If LCase(Mid$(.Value2, Len(.Value2) - 1, 1)) = "m" And _
IsNumeric(Left$(.Value2, Len(.Value2) - 2)) Then
.Value = "-" & Left$(.Value2, Len(.Value2) - 2) * 1000000
End If
Else
If LCase(Right$(.Value2, 1)) = "m" And _
IsNumeric(Left$(.Value2, Len(.Value2) - 1)) Then
.Value = Left$(.Value2, Len(.Value2) - 1) * 1000000
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"amirstal" wrote in message
ups.com...
On Jun 20, 3:16 pm, "Bob Phillips" wrote:
If you enter it like so 1M- (Excel doesn't like -1M), then you can use

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False

With Target
If Right$(.Value2, 1) = "-" Then
If LCase(Mid$(.Value2, Len(.Value2) - 1, 1)) = "m" And _
IsNumeric(Left$(.Value2, Len(.Value2) - 2)) Then
.Value = "-" & Left$(.Value2, Len(.Value2) - 2) * 1000000
End If
Else
.Value = Left$(.Value2, Len(.Value2) - 1) * 1000000
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"amirstal" wrote in message

ups.com...

On Jun 20, 12:28 pm, "Bob Phillips" wrote:
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False

With Target
If LCase(Right$(.Value2, 1)) = "m" And _
IsNumeric(Left$(.Value2, Len(.Value2) - 1)) Then
.Value = Left$(.Value2, Len(.Value2) - 1) * 1000000
End If
End With

ws_exit:
Application.EnableEvents = True
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

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"amirstal" wrote in message

groups.com...

On Jun 20, 11:50 am, Tom Ogilvy

wrote:
Tools=Autocorrect options . . .

--
Regards,
Tom Ogilvy

"amirstal" wrote:
How can I make excel know that when I type 1M in a cell the
number
1,000,000 should appear in that cell (2M will appear as 2,000,000
etc)?

Thanks,

Amir

Thanks. But is there a way to make a rule so I won't have to enter
all
manually.
For example, excel should know that 1.2m means 1200000 and 3.873m is
3873000...

This is great. Thanks very much.
However, when I try to use with the negative sign, it does not work
for example: if I type -0.3m in order to get -300000, I am getting an
error message saying "The formula you typed contains an error."

Thanks. The second solution did not work as it multiplies every number
by 100000 - even one without an "m". I can use the first solution you
gave me and write "0.3-m" in order not to confuse excel, unless you
are willing to look at the second solution one more time and fix it.