Change formatting of a cell to display currency with decimal

[Tentonhammer69]

so what I need to do is add labor cost and parts cost together to get a total that is all correct all I want to do is be able to type a number in a cell with no decimal point and have it automatically change it to currency with dollars and cents with the last two numbers always being cents.

example if I type 1234 when I move to the next cell I want it the change automatically to $12.34, if i type 123 I want it to display $1.23.

 

[Rick Rothstein]

so what I need to do is add labor cost and parts cost together to get a total that is all correct all I want to do is be able to type a number in a cell with no decimal point and have it automatically change it to currency with dollars and cents with the last two numbers always being cents.

example if I type 1234 when I move to the next cell I want it the change automatically to $12.34, if i type 123 I want it to display $1.23.

Is this functionality to apply to certain columns (if so, which columns and on what sheet)... or all the cell on a specific worksheet (if so, which sheet)... or for every cell in the entire workbook?

 

[Tentonhammer69]

it would be for two columns on the first five sheets out of thirteen

 

[Rick Rothstein]

it would be for two columns on the first five sheets out of thirteen

Which columns on each sheet?

 

[Tentonhammer69]

columns R and S

 

[Rick Rothstein]

Is this functionality to apply to certain columns (if so, which columns and on what sheet)... or all the cell on a specific worksheet (if so, which sheet)... or for every cell in the entire workbook?

it would be for two columns on the first five sheets out of thirteen

Which columns on each sheet?

While it would be better if you had given us the names of the sheets directly, I designed the following event code to work on the first 5 sheets in your workbook (let's hope your users do not slide the tabs around) in Columns R and S on each of those sheets.

[table="width: 500"]
[tr]
	[td]Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If Sh.Index < 6 Then
    If Target.Column = 18 Or Target.Column = 19 Then
      If Not Target.Value Like "*[!0-9]*" Then
        Target.Value = "$" & Target.Value / 100
      End If
    End If
  End If
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL Workbook Event Code
------------------------------------------------------
If you are new to event code procedures, they are easy to install. To install it, find the "ThisWorkbook entry in the "Project-VBAProject" window within the VB editor window and double click it. This will open up the code window for the workbook. Copy/Paste the event code above into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Tentonhammer69]

Column names are as follows
"Week1","Week 2", "Week 3", "Week 4", "Week 5"

 

[Rick Rothstein]

Column names are as follows
"Week1","Week 2", "Week 3", "Week 4", "Week 5"

I presume you mean Sheet names, not column names, correct?

Your first name (Week1) does not have a space between the word "Week" and the number "1" whereas all the others have a space there... did you mean to write that or are all the names structured the same? If the same, which is it... with or without the space?

 

[Tentonhammer69]

Sorry yes I meant sheet names not column names and how I have the sheet names typed out is correct they forgot the space for week one I just haven't changed it yet because I would have to change all the tracking formulas for that sheet

 

[Tentonhammer69]

okay I pasted the code and it works great if what you needed the sheet names for will work better that would be great but if all that is for is to make sure it works on those sheets if they are moved I am the only one that uses the work book to the tabs will never move thank you for the help and the quick response