Indian Number format

Microsoft Windows xp inside out, second...
April 16, 2010 at 23:31:52
Specs: Windows XP, pentium 4 3.00Ghz. 1gb ram
How can i convert numbers in Col.D, F and H to indian number format?. That is, if i type 1234567890 in col.H it should be converted to 1,23,45,67,890.00.

I can do this using following formula but I need worksheet change macro that works in Col.D,F and H.

The formula i am using to convert is:
=REPLACE(TEXT(B2,"##"",""##"",""##"",""##"",""##"",""##"",""###.00"),1,6INT((LEN(B2)>3)+(LEN(B2)-4)/2),"")


See More: Indian Number format

Report •


#1
April 17, 2010 at 06:29:55
Stolen without permission from:

http://www.ozgrid.com/forum/showthr...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c
    If Target.Cells.Count = 1 Then
        Select Case Target.Value
        Case Is >= 1000000000
            Target.Cells.NumberFormat = "##"",""00"",""00"",""00"",""000.00"
        Case Is >= 10000000
            Target.Cells.NumberFormat = "##"",""00"",""00"",""000.00"
        Case Is >= 100000
            Target.Cells.NumberFormat = "##"",""00"",""000.00"
        Case Else
            Target.Cells.NumberFormat = "##,###.00"
        End Select
    Else
        For Each c In Target
            Select Case c.Value
            Case Is >= 1000000000
                c.NumberFormat = "##"",""00"",""00"",""00"",""000.00"
            Case Is >= 10000000
                c.NumberFormat = "##"",""00"",""00"",""000.00"
            Case Is >= 100000
                c.NumberFormat = "##"",""00"",""000.00"
            Case Else
                c.NumberFormat = "##,###.00"
            End Select
        Next c
    End If
End Sub


Report •

#2
April 18, 2010 at 21:33:48
And how i can do this for Col.D, F and H only? Because there are some numbers which need not to be in this format such as account number etc.

Report •

#3
April 19, 2010 at 04:31:24
It's customary to say "Thanks" for the first suggestion before asking for more help.

Report •

Related Solutions

#4
April 19, 2010 at 23:04:09
Its always there in my heart though it didn't appear in the forum. I
tried changing this line:
If Target.Cells.Count = 1 Then

To:

If Target.Column = 4 Then '(for col.D)

but did not work.


Report •

#5
April 20, 2010 at 05:43:37
Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c
     Select Case Target.Column
      Case 4, 6, 8
        If Target.Cells.Count = 1 Then
          ...
          ...
          ...
        End If
     End Select
End Sub






Report •

#6
April 20, 2010 at 21:53:58
Thanks again DerbeDad03 for your assistance.

Report •

Ask Question