Macro to format data

[howard]

I have the folowing values thast have been imported in Col J. I need a macro that will format these values as #,##0.00;(#,##0.00) for eg if the values appears as 4.384,21 it must be formatted as 4,384.24 and not 4.384,21. Negaitives appearing as 2.384,34- should be formatted as (2,384.34) and not 2.384.34

Your assistance will be most appreciated

420110 92030.xls
	J	K	L	M
16	4.384,21	97
17	2.384,34-	97
18	1.457,38	25-
19	31,40	25-
20	2.140,09	25-
21	6.450,84	25-
22	2.228,08	25-
23	846,45	25-
24	164,79	25-
25	77,52	25-
26	12.323,74	25-
27	8.892,00	25-
28	2.772,71	25-
Page 1

 

[CWatts]

Scrapped my post - I totally misinterpreted your number format needs... brb.

 

[CWatts]

Updated now.

Sub NumberFormat()
For Each c In Selection
    c.Value = WorksheetFunction.Substitute(c.Value, ",", "(DEC)")
    c.Value = WorksheetFunction.Substitute(c.Value, ".", ",")
    c.Value = WorksheetFunction.Substitute(c.Value, "(DEC)", ".")
    
    If Right(c.Value, 1) = "-" Then
        c.Value = Left(c.Value, Len(c.Value) - 1)
        c.Value = c.Value * -1
    End If
    
    c.Value = c.Value + 0
    c.NumberFormat = "#,##0.00;(#,##0.00)"
    
Next
    
End Sub

 

[CWatts]

Just highlight the cells and run the macro.

 

[howard]

Thanks for the reply and your help, much appreciated

 

[howard]

Is there any way to amend the Macro so that if the number format in Col M4 to the last row in Col M is in the correct format to exit the macro, otherwise to execute the macro?

If so, please amend the macro.

Your assistance in this regard will be most appreciated