Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Nov 2003
    Location
    Virginia
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Negative Cell (Windows XP)

    I need to make a cell show a negative number no matter if a user inputs a positive or a negative number. How can I accomplish this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Negative Cell (Windows XP)

    If it's just a matter of display, you can set a custom number format for the cell (Format | Cells..., Number tab, Custom category):
    - If you want all numbers including 0 to be preceded by a minus sign: <code>-0;-0</code>
    - If you want all numbers except 0 to be preceded by a minus sign: <code>-0;-0;0</code>

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative Cell (Windows XP)

    If you only want the data to appear negative, but retain the positive value:
    Right click on the cell
    On the number tab, choose Custom from the Category list
    In the type box - enter -#;-#,0 <this will display 0 (zero) as 0

  4. #4
    New Lounger
    Join Date
    Nov 2003
    Location
    Virginia
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative Cell (Windows XP)

    I need the number to take on the value of the negative.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Negative Cell (Windows XP)

    That's not what you asked - you only mentioned "show", so both mbarron and I assumed it was only for display.

    Right-click the sheet tab.
    Select View Code from the popup menu.
    Copy the following code into the worksheet module:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A1"), Target) Is Nothing Then
    Application.EnableEvents = False
    If IsNumeric(Range("A1")) Then
    If Range("A1") > 0 Then
    Range("A1") = -Range("A1")
    End If
    End If
    Application.EnableEvents = True
    End If
    End Sub

    Replace A1 with the address of the cell or cells you want to contain negative values.
    Switch back to Excel to test.

  6. #6
    New Lounger
    Join Date
    Nov 2003
    Location
    Virginia
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative Cell (Windows XP)

    When I copied the code into the worksheet module and changed all the ("A1") to( "A1:E50") the code did not work. Am I missing something?

    Thanks

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Negative Cell (Windows XP)

    Try this version. It loops through the cells and checks each.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Range("A1:E50"), Target) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Range("A1:E50"), Target).Cells
    If IsNumeric(oCell) Then
    If oCell > 0 Then
    oCell = -oCell
    End If
    End If
    Next oCell
    Application.EnableEvents = True
    End If
    End Sub

  8. #8
    New Lounger
    Join Date
    Nov 2003
    Location
    Virginia
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative Cell (Windows XP)

    That worked perfectly. Thanks!

Posting Permissions

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