For-Next loop is not working for multi-cell selection

[mdd16]

Hello there,

I am puzzled why my for next loop isn't working in the code pasted below. I am trying to convert the cells in selected range to indian number format where there is comma different from rest of the world.

Normal Format for number in 5 digits or less
Lacs Format for number in 7 or 8 digits
Crores Format for number in 9 digits or more

The code "nFmt_India" is working fine for individual cell but the when I apply to a larger selection, it does not work correctly.

Please help..

Sub nFmt_Normal()
 For Each c In Selection.Cells
    c.NumberFormat = "###,###,###"
Next c
End Sub

Sub nFormat_Lacs() ' 6 or 7 digits
    Selection.NumberFormat = "###\,##\,###"
End Sub

Sub nFormat_Cr() '8 digits or more
    Selection.NumberFormat = "###\,##\,##\,###"
End Sub

Sub nFmt_India()
Dim c As Range

 For Each c In Selection.Cells
        If c.value < 100000 Then ' 5 digits or less
            nFmt_Normal
        ElseIf c.value >= 100000 And c.value < 10000000 Then ' 6 or 7 digits
            nFormat_Lacs
        ElseIf c.value >= 10000000 Then '8 digits or more
            nFormat_Cr
        End If
    Next c
End Sub

 

[Akuini]

You don't need separate subs.

Sub nFmt_India()
Dim c As Range

 For Each c In Selection.Cells
        If c.Value < 100000 Then ' 5 digits or less
            c.NumberFormat = "###,###,###"
        ElseIf c.Value >= 100000 And c.Value < 10000000 Then ' 6 or 7 digits
            c.NumberFormat = "###\,##\,###"
        ElseIf c.Value >= 10000000 Then '8 digits or more
            c.NumberFormat = "###\,##\,##\,###"
        End If
    Next c
End Sub

 

[mdd16]

thanks so much.. it works

 

[Akuini]

You're welcome.