forcing cell format to be negative currency?

[Guest]

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

 

[Norman Jones]

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!

 

[Guest]

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.

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

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

 

[Norman Jones]

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
'<<=============