Formatting is visual only.

Your data are still positive.

You must use the minus sign or event code.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10" '<<<adjust to suit
Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = .Value * -1
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code" Copy/paste the code into that
sheet module.

Edit the range to suit and Alt + q to return to Excel.


Gord Dibben MS Excel MVP

On Thu, 27 May 2010 13:22:27 -0700, EarthAngel
wrote:

I used custom format so that I could automatically enter several negative
numbers without keying in the minus sign each time. However the column did
not calculate correctly. What else can I try.