I just got an unbelievable behavior from excel when i did the following
value copying:
-----------------------------
Sub mycopy()
Range("A2:B2").Value = Range("A1:B1").Value
End Sub
-----------------------------

I can't figure this one out (Excel 2010). On any cell that has accounting format the first number of the value only displays
the right side of the number. Almost as if the number is cut in half. If I change the format to currency this does not
happen.

Can anyone help me with custom formatting of numbers - i am trying to get a version of the
"accounting" format with brackets for negatives instead of a minus sign. At the moment i am using the custom format

"£000.00;£ (00,000.00)"

but then I have to vary the number of spaces in every cell depending on how
many characters are in the numbers, to get the £ at the left hand side of the cell (and it doesn't exactly line up). Is there
an easier way to do this?

Protect sheet will not solve my problem, and neether will the otherwise excellent post at
http://www.eggheadcafe.com/software/aspnet/33295991/formatting-not-protected-when-paste.aspx.

I simply need
someone to help me with this code, which I have tried unsuccessfully to adopt from another ozgrid page. Even with this code
in the sheet's module, when I enter a number with a dollar sign, accounting format is dropped. I really don't want that. I
want it to STAY in accounting format

VB:

Range)
'ENFORCE ACCOUNTING FORMAT IN CELL I54
'Do nothing if more than one cell is changed or if content is deleted
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$1" Then
'First make sure target is a number
If IsNumeric(Target) Then
'Stop potential runtime errors and halting code
'On Error Resume Next
'Turn off all events so operation does not put the code into a loop
Application.EnableEvents = False
'HERE FOLLOWETH THE CODE WE'RE AFTER
Target.Cells.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
'Turn events back on
Application.EnableEvents = True
'Allow runtime errors again
On Error Goto 0
End If
End If
End Sub

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I have built some worksheets with check figures that should all come to zero. I use the accounting format for numbers, and in
the same worksheet, some of the check figures will show a dash -, and others will show 0.00 or (0.00).

I know
this can happen when the formula calculates a number with more than 2 decimal places, but if I round all of my formulas and
all of the referenced cells to 2 decimal places, it should calculate zero correctly, and the accounting format should show
the dash.

Does anybody know why this happens and how to fix it?

(This is not a really important issue,
except that I am a perfectionist and my worksheets look sloppy if there are a mix of dashes and zeros!)

I have formatted cells with the accounting format with no symbol. Two cells
that compute to zero show differently. One shows 0.00 and the other shows a
"-" dash. I have rechecked all of the cells that are included in the formula
for each and I cannot find any inconsistencies; all are formatted the same.
I realize that this seems petty but for the life of me I cannot determine why
the same formatting shows two different designations for zero. I prefer the
"-" dash.

When I try to create a custom list using text cells and accounting formated
($99.99) cells, I get an error message that cells without simple text will be
ignored. What is simple text and how can I get numerical values included in
the list? They have to be numbers for future calculations.

I have Excel 2002 (XP) and want to display negative numbers in parentheses in
ACCOUNTING FORMAT instead of with a minus sign. I know this is possible as a
standard feature (I have done it before on a previous machine) and I think
there was a KB patch for this flaw. Anyone know of where I might find the
fix?

When selecting Accounting as the number format, negative figures used to
appear in brackets. That is now no longer the case. Negative figures using
the Accounting format now appear with a minus in front. I have never used
any version of Excel other than Office XP.

I am using Excel 2003. I am attempting to use the Accounting format with numbers that should not have
any decimal places (although what is entered might have a decimal place). The numbers line up fine on the right, however,
the dollar signs on the left are not lining up. It looks something like:

I have a lot of data which are in different currencies and I have created a new tab in my worksheet to
convert all the data into a chosen currency.

The calculations work fine, but not the formating. When the currency
on the scroll down list ("currency input"! B8) is on "GBP", I want all the values from column C to G in
"currency converted data"!to change to £. There are up to 5 different currencies. The formatting only seems to work
when I click on apply in the managed rules within conditional formatting. I would like it this to work automatically whenever
the currency is changed in the scroll down list.

I don't undertsand what could avoid the formatting to update
automatically

I have attached a sample spreadsheet with the same layout as my current spreadsheet. There are 3
tabs: "currency input", "currency converted data" & "data"

I have formatted cells with the accounting format with no symbol. Two cells
that compute to zero show differently. One shows 0.00 and the other shows a
"-" dash. I have rechecked all of the cells that are included in the formula
for each and I cannot find any inconsistencies; all are formatted the same.
I realize that this seems petty but for the life of me I cannot determine why
the same formatting shows two different designations for zero. I prefer the
"-" dash.

I have created a cell "A" to display the difference between two balances (cells "B" & "C") on sheet 1.

Cell "A" is
formatted to be displayed as an "accounting" number with "$".
Cell "A" has also been formatted to turn red when the number is negative, green when the number is positive and no fill when
the number equals zero.

Cell "B" is the sum of many numbers (checkbook type balances) from multiple other sheets
all of which are set up identically.
Cell "B" has also been formatted to display as an "accounting" number.

Cell "C" is the sum (balance) of a list of
numbers from sheet 1 only and is also formatted as an "accounting" number.

My question is that...
1)Cell "A" will sometimes be displayed as a "currency" number as different entries are made within other connected sheets
then turn back into an "accounting" number with other entries.
2)Cell "A" will sometimes turn red when the value equals zero.

There seems to be no consistancy with which this
happens. To further prove this, I have tried to enter the figure $1.10 onto a sheet "2" which cell "B" will sum and connect
to cell "A". The result is an "accounting" number. When I replace the $1.10 figure with $1.11 the result is a "currency"
number in cell "A". This same irregulartiy happens with cell "A"'s coloring.

My preference of formatting cells with dollar signs is to align the dollar
sign adjacent to the leftmost number rather than aligning it to the left side
of the cell. Excelâ€™s Currency formatting feature does this whereas the
Accounting format aligns the $ to the left side of the cell.

I would like to use the toolbar to easily format a
cell using the Currency
formatting style so the dollar sign is aligned as I desire rather than the
longer process of using the Excel menu etc.

The dollar sign icon on the Formatting toolbar uses the
Accounting
formatting style rather than the Currency style. I cannot figure a way to
get an icon on the toolbar to format a cell using the Currency formatting.

Can anyone tell me how to change the currency format in the styles area
to match the format in the manner as when you got to Format Cell
Currency.

For some reason the justification is in the affected cell is different
for the currency format.

If you change in the Format Cell area, the reformatted currency is
right justfied. But if you change the number format to currency in the
Style area it is not right justified but it keeps it in the accounting
format justification.

My preference of formatting cells with dollar signs is to align the dollar
sign adjacent to the leftmost number rather than aligning it to the left side
of the cell. Excelâ€™s Currency formatting feature does this whereas the
Accounting format aligns the $ to the left side of the cell.

I would like to use the toolbar to easily format a
cell using the Currency
formatting style so the dollar sign is aligned as I desire rather than the
longer process of using the Excel menu <FORMAT> <CELL> etc.

The dollar sign icon on the Formatting
toolbar uses the Accounting
formatting style rather than the Currency style. I cannot figure a way to
get an icon on the toolbar to format a cell using the Currency formatting.

I have a textbox on a userform that people will be entering dollar values into. I'd like to have the
value displayed in $ format, but when the value is copied over to another spreadsheet, I'd like it to be formatted in
Accounting format. I can make it do Currency format, but I cannot figure out how to do the Accounting format.

Hi All,
I am struggeling to format a column to display currency values formated with a different currency mark than the local system
currency.
Right now, when I run the macro on my machine I get my local currency (Israely Shekel), and on the Client's machine I get
Euro. (I need it to be in dollars)