Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jan 2010
    Location
    Florida
    Posts
    9
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Enter positive number & have return as negative

    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

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    edh001 (2011-11-01)

  4. #3
    New Lounger
    Join Date
    Jan 2010
    Location
    Florida
    Posts
    9
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks. I was almost sure there wasn't a way to do this

    Ed

  5. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. The Following User Says Thank You to rory For This Useful Post:

    edh001 (2011-11-01)

  7. #5
    New Lounger
    Join Date
    Jan 2010
    Location
    Florida
    Posts
    9
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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

  8. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    That's easy enough - just change the other formula from =SUM(...) to =-SUM(...)
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #7
    New Lounger
    Join Date
    Jan 2010
    Location
    Florida
    Posts
    9
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hey thanks this is great.

    Ed

  10. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by rory View Post
    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"
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •