VBA coding only working in the first sheet. It is not working in all the sheets

Dear Sirs,

I am just started macror/vba coding.

I have created a coding for displaying number in Indian currency in indian format(comma separator) in excel 2007. Though it is working in the first sheet It is not working in all the sheets. Please guide me on this.

I would like to learn how to protect the coding. If am sending this file to others other users should not view the coding.

code i have used as follows

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Value >= 1000 And Target.Cells.Value < 10000 Then
    Target.Cells.NumberFormat = "#"",""###.00"
ElseIf Target.Cells.Value >= 10000 And Target.Cells.Value < 100000 Then
    Target.Cells.NumberFormat = "##"",""###.00"
ElseIf Target.Cells.Value >= 100000 And Target.Cells.Value < 1000000 Then
    Target.Cells.NumberFormat = "#"",""##"",""###.00"
ElseIf Target.Cells.Value >= 1000000 And Target.Cells.Value < 10000000 Then
    Target.Cells.NumberFormat = "##"",""##"",""###.00"
ElseIf Target.Cells.Value >= 10000000 And Target.Cells.Value < 100000000 Then
    Target.Cells.NumberFormat = "#"",""##"",""##"",""###.00"
ElseIf Target.Cells.Value >= 100000000 And Target.Cells.Value < 1000000000 Then
    Target.Cells.NumberFormat = "##"",""##"",""##"",""###.00"
ElseIf Target.Cells.Value >= 1000000000 And Target.Cells.Value < 10000000000 Then
    Target.Cells.NumberFormat = "###"",""##"",""##"",""###.00"
End If
End Sub

Thanks & Regards
N.Srinivasan

That macro would have to be inserted specifically into the sheet module of each sheet where you want it to work. If you have 3 sheets, then you will right-click on each sheet tab, VIEW CODE, and paste in the code. So that's 3 modules, 3 copies of that same macro.


Here's a version you can put in the ThisWorkbook module instead, this one macro would then watch ALL sheets and act accordingly on all sheets:

Option Explicit

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Cells.Value >= 1000 And Target.Cells.Value < 10000 Then
        Target.Cells.NumberFormat = "#"",""###.00"
    ElseIf Target.Cells.Value >= 10000 And Target.Cells.Value < 100000 Then
        Target.Cells.NumberFormat = "##"",""###.00"
    ElseIf Target.Cells.Value >= 100000 And Target.Cells.Value < 1000000 Then
        Target.Cells.NumberFormat = "#"",""##"",""###.00"
    ElseIf Target.Cells.Value >= 1000000 And Target.Cells.Value < 10000000 Then
        Target.Cells.NumberFormat = "##"",""##"",""###.00"
    ElseIf Target.Cells.Value >= 10000000 And Target.Cells.Value < 100000000 Then
        Target.Cells.NumberFormat = "#"",""##"",""##"",""###.00"
    ElseIf Target.Cells.Value >= 100000000 And Target.Cells.Value < 1000000000 Then
        Target.Cells.NumberFormat = "##"",""##"",""##"",""###.00"
    ElseIf Target.Cells.Value >= 1000000000 And Target.Cells.Value < 10000000000# Then
        Target.Cells.NumberFormat = "###"",""##"",""##"",""###.00"
    End If
End Sub

Put the code given in post #2 in the ThisWorkbook module.

http://screencast.com/t/YliZx6oqUOEj

I don't see an attachment. Try again, and make sure the macro I gave you is already installed so I can see what you tried. Thanks.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

I opened your file and started clicking on cells in various sheets, every cell I clicked on reformatted itself.

Thank you very much,
How to make this happen after pressing enter key or tab
ie while entering the number it should get formatted by itself

Change this:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

To this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

The code want to change the values and therefor you get an error.

if you put an ' before above line in the code, you will be able to delete, insert, erase, rows.

After you've done that you have to delete the ' for the code.

With other words, you have to rewrite the code so you will be able to do delete, insert and erase.

I can't help you with that, maybe someone else can make it for you.

Add this line at the top:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub