Change negative numbers to positive numbers and positive numbers to negative numbers in Excel by using VBA. These macros reverse the sign of cells in Excel.

The first macro was submitted by wellsrPRO community member, Jomili, and the second macro is a slight modification I made to handle non-numeric. All you have to do is select the cells you want to negate, and run the macro of your choice.


Reader’s Note: This article is part of a series featuring macros submitted by my incredible wellsrPRO community members. These articles are similar to my Code Library articles in that they usually won’t contain an accompanying detailed tutorial.

If you’re an existing wellsrPRO member, don’t forget to submit your own macro to the wellsrPRO community using the Share My Macros button.

wellsrPRO users can automatically import this community submission directly into their spreadsheet. Just look for “Community Submissions” in the Auto-Import dropdown menu.


Reverse Sign of Cells in Excel

Created by Jomili

Sub PlusMinus()
    'DEVELOPER: Submitted by wellsrPRO community member "Jomili"
    'Changes the sign on a selected range of numbers
    Dim cell As Range
    If Application.Count(Selection.Cells) = 1 Then
        MsgBox "You CAN'T be that Lazy!  Select more cells!", vbOKOnly + vbExclamation, "Give Me a Break"
        Exit Sub
    End If
    On Error Resume Next    'copes with cells that are not numeric
    For Each cell In Selection.SpecialCells(xlCellTypeConstants, 23)
        cell.Value = -cell.Value
    Next cell
End Sub

If you have a lot of cells selected, you’ll want to pair this macro with SpeedOn and SpeedOff subroutines previously submitted by Jomili to speed up your macro.

There’s a lot to unpack here. To save time and become really good at VBA, take a look at our VBA Reference Guides. They have over 180 tips and 135 pre-built macros covering the 100 most important topics in VBA.

Enhanced way to change signs in Excel

Sub PlusMinus_modified()
    'DEVELOPER: Ryan Wells (wellsr.com)
    'Changes the sign on a selected range of numbers
    Dim cell As Range
    Dim v As Variant
    If Application.Selection.Cells.Count = 1 Then
        v = MsgBox("Only one cell is selected. Are you sure you want to change the sign of only one cell?", vbYesNo, "Change 1 cell?")
        If v = vbNo Then Exit Sub
    End If

    For Each cell In Intersect(Selection, Selection.SpecialCells(xlCellTypeConstants, 23)) 'negate values in selection, excluding formulas
        If IsNumeric(cell.Value) Then cell.Value = -cell.Value 'change negative to positive and positive to negative
    Next cell
End Sub

These macros will not reverse the signs of cells containing formulas and they won’t add a negative sign to text or dates. The VBA macros are smart enough to see if the cell is numeric before trying to negate them!

Instead of displaying a funny message, like Jomili did in the first macro, the second macro reminds the user that they only have one cell selected and it asks the user if they really want to change the sign of just that one cell. The SpecialCells portion of the For Each loop makes sure that formulas are ignored.

The Intersect function is necessary because the SpecialCells method will expand beyond your original selection if you only have one cell selected. In other words, even if you had only one cell selected, it would try to negate all the cells in your spreadsheet! We wouldn’t want that, would we?

That’s all for this tutorial. I hope you’ll share it on Facebook and Twitter. When you’re ready to take your VBA to the next level, subscribe using the form below.


Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I developed this unique 3-part free Excel training program to help you quickly learn VBA in a natural setting: right inside Excel. I'm also going to give you my entire personal macro library for free.

Let me join the free wellsrPRO VBA Training program

If you’re ready to free up your time in the office, visit my Excel Add-ins page for some great automation products.