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]

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

Yes, the only reason I wanted the sheet names was to protect against the sheets being moved around. Since that won't be a problem for you, and since the code I posted earlier works for you, I am assuming your problem is now resolved.

 

[Tentonhammer69]

Scratch that it worked when I typed in the first cell but not when I put numbers in the cell right below it. I should have thought of this earlier there is a formula in cells S and T (I added a column since I asked the question) I changed the column numbers in the code to reflect that. There is a formula in cell 3 [=sum(s4:s360)] in both columns I don't know if that is an Issue or not. When I tested the code I typed 111 in cell s4 and it came up with $1.11, but when i typed 6000 to get $60.00 it changed it to $1 and the formula said $0.60.

 

[Rick Rothstein]

There is a formula in cell 3 [=sum(s4:s360)] in both columns I don't know if that is an Issue or not.

You have your formulas in S3 and T3? Yes, that would be an issue as the code is monitoring those cells... the code is meant to work with manual entries and your original postings indicated you needed the functionality for the entire column. Based on your formula, I am assuming you only need this functionality for cells S4:T360. Below is my code modified to monitor only those cells, but before you install it, remove my previous code and then select Columns S and T and change their cell format back to General, then install this code...

[table="width: 500"]
[tr]
	[td]Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Dim Cell As Range
  If Sh.Index < 6 Then
    If Not Intersect(Target, Range("S4:T360")) Is Nothing Then
      For Each Cell In Intersect(Target, Range("S4:T360"))
        If Not Cell.Value Like "*[!0-9]*" And Len(Cell.Value) > 0 Then
          Cell.Value = "$" & Cell.Value / 100
        End If
      Next
    End If
  End If
End Sub[/td]
[/tr]
[/table]