Macro to Toggle Positive to Negative

November 09, 2021 - by Bill Jelen

Problem: My debits came in as credits. I need to quickly change the sign on all numbers in a range.

Strategy: Put a -1 in an empty cell. Copy that cell. Select the range of cells to toggle. Right-click, Paste Special. In the Paste Special dialog, use Values, Multiply, OK. Or - add this macro to your Personal Macro Workbook:

Sub ToggleSign()
	On Error Resume Next
	For Each cell In Selection
		cell.Value = -cell.Value
	Next cell
	On Error GoTo 0
End Sub

Assign the macro to a shortcut key or a toolbar icon using one of the next two topics.

To use the macro, select the range that you want to toggle. Press a shortcut key to run the macro. Any text in the range will generate an error, but the code above will ignore errors, so text cells will be unchanged.

This article is an excerpt from Power Excel With MrExcel

Title photo by Tyler Easton on Unsplash