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

The simplest way is to use two cells:

Enter your data in A1 and in B1:

=-A1

Thus saving a keystroke.

Another way involves event macros.
--
Gary''s Student - gsnu200755


"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

(General);(General)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"GeeTee" wrote in message
...
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

Hi,

Not with a format because that will make the number look negative when in
fact it would not change the underlying value. This may help. Right click the
sheet tab, view code and paste this in:-

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:B10")) Is Nothing Then ' Change to suit
If IsNumeric(Target) Then
Application.EnableEvents = False
Target.Value = Target.Value * -1
Application.EnableEvents = True
End If
End If
End Sub

Mike

"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

But be aware that this changes only the way the number is shown, not the
underlying value. So if the value is then used elsewhere in a formula, the
positive value will be used, which I would find very confusing. Whilst this
is exactly what the OP asked, it may well not be what was wanted! Certainly
the OP should be made aware of this.

"Bob Phillips" wrote in message
...
(General);(General)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"GeeTee" wrote in message
...
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

To you point:

Format Cells... Number Custom
"-"General" (only joking)"

--
Gary''s Student - gsnu200755


"Stephen" wrote:

But be aware that this changes only the way the number is shown, not the
underlying value. So if the value is then used elsewhere in a formula, the
positive value will be used, which I would find very confusing. Whilst this
is exactly what the OP asked, it may well not be what was wanted! Certainly
the OP should be made aware of this.

"Bob Phillips" wrote in message
...
(General);(General)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"GeeTee" wrote in message
...
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

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

I tend to respond to the question asked unless it is ambiguous or clearly
mis-stated.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Stephen" <none wrote in message
...
But be aware that this changes only the way the number is shown, not the
underlying value. So if the value is then used elsewhere in a formula, the
positive value will be used, which I would find very confusing. Whilst
this is exactly what the OP asked, it may well not be what was wanted!
Certainly the OP should be made aware of this.

"Bob Phillips" wrote in message
...
(General);(General)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"GeeTee" wrote in message
...
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

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