Numberformat with many columns at one go

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
137
Hello

I want to numberformat with many columns at one go

Can i

Code:
ws.Columns(3, 5, 9, 11, 23, 27).NumberFormat = Trim(Replace(Format(String(Len(Int([B][COLOR=#ff0000]Cell.value[/COLOR][/B])) - 1, "#"), " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) & ".00"
Also Cell.value in red requires proper syntax
Will the above format round off if "No" then how can i round off


Thanks
SamD
 
Last edited:

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,777
Hi Sam

I've seen this number format in posts for currency in India.

If you want to use a different numberformat for each cell you'll have to loop through all the cells and add the format. This is not the usual solution. Also if some number would change you'd have to set the format again.


The usual solution is to use conditional formatting, adding conditions for different number brackets, for ex.

- for <1000 use "0.0",
- for <100000 use "0,000.00"
- for <10000000 use "0\,00\,000.00"
etc.

This way you can do it in 1 go for all the columns.
Also if a number changes the format updates automatically.
 

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
137
pgc01

Sorry for very late reply.
do you mean something like this

Code:
dim nos as Integer
if nos < 1000
ws.Columns(3, 5, 9, 11, 23, 27).NumberFormat = Trim(Replace(Format(String(Len(Int(Cell.value)) - 1, "#"), " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) & "[B][COLOR=#ff0000].0[/COLOR][/B]"
ElseIf nos < [COLOR=#333333]100000
[/COLOR]ws.Columns(3, 5, 9, 11, 23, 27).NumberFormat = Trim(Replace(Format(String(Len(Int(Cell.value)) - 1, "#"), " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) & "[B][COLOR=#ff0000]0\,00\,000.00[/COLOR][/B]"
End If
Also i was having doubt using the following syntax for columns at one go. Is the syntax right
Code:
[B][COLOR=#0000cd]ws.Columns(3, 5, 9, 11, 23, 27).NumberFormat[/COLOR][/B]
Frankly speaking i am posting this post where no MS-Excel is installed. Need to check when i am on my desk

Thanks for the suggestion
Samd
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,777
Hi Sam

No, I mean Conditional Formatting.

I don't have excel here but you can try record a macro while setting the conditional formatting manually and go from there.

If you have problems post back and I'll post some code tonight (GMT).
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,777
This is an example.

Open a new workbook and run this code.

It sets your format in some columns in Sheet1.

Write some numbers and see how they are formatted.

Try:

Code:
Sub NumberFormatColumns()
Dim ws As Worksheet
Dim r As Range

Set ws = Worksheets("Sheet1")

With ws
    Set r = Union(.Columns("A"), .Columns("E"), .Columns("G"))
End With

With r.FormatConditions
   
    .Delete
   
    .Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=1000").NumberFormat = "0.00"

    .Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=100000").NumberFormat = "0\,000.00"

    .Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=10000000").NumberFormat = "0\,00\,000.00"

    .Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=1000000000").NumberFormat = "0\,00\,00\,000.00"

    .Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=100000000000").NumberFormat = "0\,00\,00\,00\,000.00"

    .Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=10000000000000").NumberFormat = "0\,00\,00\,00\,00\,000.00"

End With

End Sub
 

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
137
Pgc01

Thanks i tried it works beautifully. :)
But what i desire is when any numeric value typed in cols A, E and G want to round off to nearest value
as per your solution i typed in col A 134.03 cell values displayed as 134.03 instead of 134

and if cell value is 134.50 then it should display 135. Any possibilities

Thanks
SamD
 
Last edited:

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
137
Sorry But how will Union help ?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,926
Office Version
2010
Platform
Windows
I want to numberformat with many columns at one go

Can i

Code:
ws.Columns(3, 5, 9, 11, 23, 27).NumberFormat = Trim(Replace(Format(String(Len(Int([B][COLOR=#ff0000]Cell.value[/COLOR][/B])) - 1, "#"), " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) & ".00"
Also Cell.value in red requires proper syntax
Will the above format round off if "No" then how can i round off
I think you may find my mini-blog article here to be of some interest...

http://www.excelfox.com/forum/showthread.php/375-Excel-Number-Format-Indian-Style-Comma-Separation
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,926
Office Version
2010
Platform
Windows
Thanks Rick the same format i;ve adopted from .
http://www.excelfox.com/forum/showthread.php/375-Excel-Number-Format-Indian-Style-Comma-Separation

in post #1 of this thread. Really Good One
only now i require to round off the nearest value after decimal
Remove what I show in red below from this line of code from inside the For..Next loop
Code:
    C.NumberFormat = Trim(Replace(Format(String(Len(Int(C.Value)) - 1, "#"), _
                     " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", ""))[B][COLOR="#FF0000"] & ".00"[/COLOR][/B]
 

Watch MrExcel Video

Forum statistics

Threads
1,099,559
Messages
5,469,416
Members
406,650
Latest member
cc4digital2

This Week's Hot Topics

Top