Here's a question I was asked last week by Kelly (Hi Kelly!)
"I need to know how to format numbers so the decimal automatically comes up. For example, if I key 123456--I need 56 to automatically go into the decimal place."
And my reply (after cleaning the code up :-) )
Enter this into the appropiate sheet module.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim myDecimalPlace As Integer
myDecimalPlace = Len(Target) - Len(Application.Substitute(Target, "-", ""))
If myDecimalPlace > 0 Then
Application.EnableEvents = False
Target = Application.Substitute(Target, "-", "")
Target = Target / Application.Power(10, myDecimalPlace)
Target.NumberFormat = "0." & Application.Rept("0", myDecimalPlace)
Application.EnableEvents = True
I thought this was interesting because the "formatting" of the Target consists of 2 separate parts. The part to actually format, and the part to set the format and be deleted thereafter.
So intead of using something like a Custom Format or "Case 10 To 20" in code, the formatting doesn't depend on the value but on how you type it.
123- will result in 12.3 but 123-- will result in 1.23. This is far more simpler than trying to format each indivdual cell. (Ugh!)
Needless to say, other kinds of formatting are also possible.
Target.Font.Bold = True
Target.Interior.ColorIndex = 34
etc, etc, etc. Add them as you like - have fun ;-)