You can use event code behind the sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A100"
Dim cell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each cell In Target
If Left(cell.Value, 1) < "-" Then
cell.Value = cell.Value * -1
End If
Next cell
End If

ws_exit:
Application.EnableEvents = True
End Sub

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

Copy/paste into that sheet module. Adjust range to suit.


Gord Dibben MS Excel MVP

On Mon, 12 Nov 2007 04:51:01 -0800, GeeTee
wrote:

Thank guys for the prompt responses, and yes I did want to change the value
to negatve, I had tried custom format. I continually enter amounts into a
column that are neg and wanted to save a keystroke

"GeeTee" wrote:

Can I format cells so that any number entered into it is shown as negative
automatically ie not having to make the entry with a minus sign


Thanks