Numberformat with many columns at one go

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
205
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:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
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
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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]
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top