Currency format

  • Thread starter Thread starter Christina
  • Start date Start date
C

Christina

Hi, I am trying to format cells such that when I input dollar amount it will
automatically insert a period. For example to input $15.45 I type 15 then
period then 45. I would like to know what to do so when I type 1545 it shows
up as $15.45 automatically.

Thanks for your help - Christina
 
Hi Christina,

Tools>Options>Edit tab, check Fixed decimal and enter 2 into Places

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Tools/Options/Edit, check the Fixed Decimals checkbox, and ensure that 2
is in the textbox.

Note that this is a global setting.
 
Christina

If you just want the two decimal places for a range on one worksheet you can
use Worksheet event code........

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
With Target
If .Value <> "" Then
.Value = .Value / 100
.NumberFormat = "$#,##0.00"
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Select your worksheet tab and "View Code". Copy/paste into that module.

Enter 1234 in A1 and see $12.34 returned.

If your range is not contiguous you can change the Me.Range like this

("A5,C1,C5,F1,F8,I6,H3,H13,D13,C15,F18,F13")


Gord Dibben Excel MVP
 
Back
Top