forcing cell format to be negative currency?
 

I'm copying data from one WB to another. When the value is copied to a
particular cell, that cell is always a negative currency value. Here is the
code I'm using to format the cell, what can I add to force any entry to be a
negative currency value?

Range("B85:B86").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
End With

forcing cell format to be negative currency?
 

Hi Jouiooui,

Try:
'=============
Public Sub Tester()
With Range("B85:B86")
.Font.Name = "Arial"
.Font.Bold = True
.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
End With

End Sub
'<<=============

If, however, you mean exactly what you appear to say and all
values, positive and negative are to be displayed as negative
currency values, try:

'=============
Public Sub Tester2()
With Range("B85:B86")
.Font.Name = "Arial"
.Font.Bold = True
.NumberFormat = "[Red]($#,##0.00)"
End With

End Sub
'<<=============

If the second interpretation is correct, I would be intrigued to
know the underlying logic!


---
Regards,
Norman


"JOUIOUI" wrote in message
...
I'm copying data from one WB to another. When the value is copied to a
particular cell, that cell is always a negative currency value. Here is
the
code I'm using to format the cell, what can I add to force any entry to be
a
negative currency value?

Range("B85:B86").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
End With

forcing cell format to be negative currency?
 

Hi Norman,

Thanks for this code, it formats the cell, however it doesn't change the
data to be a negative. I'm using the currency in those cells in an
accounting balance sheet and I need it to be a negative. Is there a way to
automatically put a - sign in front of any currency value copied into that
cell. Thanks again.





"Norman Jones" wrote:

Hi Jouiooui,

Try:
'=============
Public Sub Tester()
With Range("B85:B86")
.Font.Name = "Arial"
.Font.Bold = True
.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
End With

End Sub
'<<=============

If, however, you mean exactly what you appear to say and all
values, positive and negative are to be displayed as negative
currency values, try:

'=============
Public Sub Tester2()
With Range("B85:B86")
.Font.Name = "Arial"
.Font.Bold = True
.NumberFormat = "[Red]($#,##0.00)"
End With

End Sub
'<<=============

If the second interpretation is correct, I would be intrigued to
know the underlying logic!


---
Regards,
Norman


"JOUIOUI" wrote in message
...
I'm copying data from one WB to another. When the value is copied to a
particular cell, that cell is always a negative currency value. Here is
the
code I'm using to format the cell, what can I add to force any entry to be
a
negative currency value?

Range("B85:B86").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
End With

forcing cell format to be negative currency?
 

Hi Jouiooui,

Try:
'=============
Public Sub Tester()
Dim Rng As Range
Dim rCell As Range

Set Rng = Range("B85:B86")

For Each rCell In Rng.Cells
With rCell
.Value = -Abs(.Value)
.Font.Name = "Arial"
.Font.Bold = True
.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
End With
Next rCell

End Sub
'<<=============


---
Regards,
Norman



"JOUIOUI" wrote in message
...
Hi Norman,

Thanks for this code, it formats the cell, however it doesn't change the
data to be a negative. I'm using the currency in those cells in an
accounting balance sheet and I need it to be a negative. Is there a way to
automatically put a - sign in front of any currency value copied into that
cell. Thanks again.