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.

Make powerful macros with our free VBA Developer Kit

This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy, along with our Big Book of Excel VBA Macros, to your email address below.

I'll take a free VBA Developer Kit

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.

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