Numberformat with many columns at one go

[SamDsouza]

Hello

I want to numberformat with many columns at one go

Can i

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

 

[pgc01]

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]

pgc01

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

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

[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]

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]

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:

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]

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

 

[SamDsouza]

Sorry But how will Union help ?

 

[Rick Rothstein]

I want to numberformat with many columns at one go

Can i

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

 

[SamDsouza]

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

SamD

 

[Rick Rothstein]

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

    C.NumberFormat = Trim(Replace(Format(String(Len(Int(C.Value)) - 1, "#"), _
                     " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", ""))[B][COLOR="#FF0000"] & ".00"[/COLOR][/B]