How do I make a cell in Excel default to the negative value?

[Guest]

Hello, I'm creating a spreadsheet, and I'd like to have some of the cells
default to negative values no matter what value is input (positive or
negative).

 

[Gord Dibben]

You cannot format a cell to be a negative number.

You could use event code to change the typed numbers to real negatives as you
enter them.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'change a number to negative
If Target.Column <> 2 Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
If Not Left(Target.Value, 1) = "-" Then
Application.EnableEvents = False
With Target
.Value = .Value * -1
End With
End If
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.

As written it operates on column 2(B) only. Adjust to suit column number or if
you have a non-contiguous range in mind post back.


Gord Dibben MS Excel MVP

On Thu, 6 Sep 2007 14:20:03 -0700, TVB Credit Manager <TVB Credit