combining number formats 2

[electricpete]

I apologize if this is too basic.

How do I combine multiple number formats for single cell

0;-0;;@ (ignore zero's)

#,### (add comma's)

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[jalvarez]

Pete:
More than one number for a single cell?
You can apply different formats for characters in a single cell, but if you put a number it will be recognized as only a number.
If you want to put more than one number with different formats, you may only do it if you declare the cell as a text. You may do it adding the apostroph ' before the number. If you want ot have it centered, add the character ^ before the number.
It will be taken as a text, and you may format the cell character by character.
Best regards
J.Alvarez

 

[palusa]

Try this (code shall be in the specific sheet):

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'
' two number formats for a single cell
'
With ActiveCell
If Not IsEmpty(.Value) Then
If IsNumeric(.Value) Then
Select Case .Value
Case Is < 1000
ActiveCell.NumberFormat = "0;-0;;@"
Case Else
ActiveCell.NumberFormat = "#,###"
End Select
End If
End If
End With
End Sub


Hope it helps

_LF

 

[electricpete]

Thanks Palusa. I think that is exactly what I need.

One small problem. I am not smart enough to figure out where to put the code or how to execute it.

Can someone please explain that part?



=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[IJR]

That is a very important question electricpete. I usually put my codes in a "module". Putting them anywhere else gets me into a maze of problems(problems here means no problem since there is simply no action)

respects
ijr

 

[electricpete]

OK. I know where to insert it as follows:

went to tools/macro/vbe.

Insert/module.

Cut pasted into the module:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'
' two number formats for a single cell
'
With ActiveCell
If Not IsEmpty(.Value) Then
If IsNumeric(.Value) Then
Select Case .Value
Case Is < 1000
ActiveCell.NumberFormat = "0;-0;;@"
Case Else
ActiveCell.NumberFormat = "#,###"
End Select
End If
End If
End With
End Sub

Close and Return to excel.

How do I execute it?



=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

I think I have figured out how to accomplish this without vba code.

1 - Set all cells to number format #,###
2 - Format/Conditional formatting, If cell value = 0 , Then set set format font-color=white.

But if anyone can explain how to execute a subroutine above I would still be interested.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[IJR]

Yeah electricpetet

I dropped by here to let you know of conditional formatting and to cancel out my earlier post on where your vba should be. Palusa told you actually that you should write your code in that specific sheet and he is right.

I keep functions in modules but I think subroutines are more sensitive to where you store them because they deal with more cells and they produce an action effecting a range of cells, while functions simply return a value.

For this particular sub, reference is made to the sheet you will process. So first keep in mind the name of the sheet you are storing data(say sheet1), then switch to VB window, in the folder area there(usually on the left) double click the "sheet1" and the code window(usually on the left) should read "worksheet",paste the code or write it on there. Then switch to excel window and type in numbers some less than 1000 and some in excess, and there will be some action everytime you move to a different cell. I tried it and it worked(though I dont know what the theme is).

respects
ijr

 

[palusa]

Hi electricpete,
apologize i was out of office & couldn't get your msg.

To activate the macro i gave you:
(1) open VB (alt F11);
(2) within the VBA project panel, locate your working file: normally you have a structure such as: "(-)VBA Project (MyProg.xls)"... and below... "(-)Microsoft Excel Obiects" ... and below "Sheet1 Sheet2 ... ThisWorkbook".
(3) Select the sheet where you want to apply this formatting by doubleclicking.
(4) In the code panel paste the routine i sent.

Voilà, you don't need to activate it: it will run in background because it traps any change in the worksheet (try it).

Of course if you intend to apply in other sheets of the same workbook repeat the above steps.

Hope it helps

_LF

 

[MigrantFirmWorker]

I apologize if I have completely missed your point, but have you tried this format:?

#,###;-#,###;;@

 

[onlyadrafter]

Hello,

Not sure whethre this is helpful, but two different formats for one cell I did once was a CUSTOM FORMAT entered like this

[>=1000]#,###;###.0



----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!