Enter positive number & have return as negative

Topic

New Reply

Can anyone help me on this item. I thought it would be easy but so far not.

I want to format cells that I enter (currency) positive numbers into but they are then returned with the negative format of Brackets & red.

Anyone offer some ifo.

Thanks

Ed

Reply | Quote

Replies

Ed,

Place the following code in the Worksheet VBA module for the the sheet you want it to operate on.

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

‘*** Limiting the Worksheet_Change event to a firing when a single cell is changed

   Dim isect As Range
   
   Set isect = Application.Intersect(Range(“A:A”), Target)
   If isect Is Nothing Then
‘     MsgBox “Ranges do not intersect”  ‘*** Debugging Statement un-comment to use.
   Else
          Target.Formula = Target.Value * -1
   End If

End Sub

Note: The Range(“A:A”) will cause this macro to operate on all cells in column A. If you need it in a different column adjust as necessary, likewise if you need it in more than one column or you can make a specific range like B7:B23 or B7:H32, etc. :cheers

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks. I was almost sure there wasn’t a way to do this

Ed

Reply | Quote

If it’s only the format you are worried about, you can do that with a custom number format, though it will not change the stored values.

Reply | Quote

Thanks, I need the sum values to be copied into another cell & need the number to reflect that it is negative. When I use custom nuber format I don’t have the facility to actually add it up to be a negative number. Unless there is a custom format to define negative.

Ed

Reply | Quote

That’s easy enough – just change the other formula from =SUM(…) to =-SUM(…)

Reply | Quote

Hey thanks this is great.

Ed

Reply | Quote

That’s easy enough – just change the other formula from =SUM(…) to =-SUM(…)

Nice trick, but I’ve sat in too many meetings where there is someone adding up the column just to be sure, or a wise guy, and this won’t look right. “I’m just sayin’ YMMV” :rolleyes: :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote