I am creating a spreadsheet that has a group of cells that is always a
negative number. I would like any number entered to default to negative in
case the negative sign is forgotten when the number is entered. Can a group
of cells be formated so they always appear as a negative nember? I am using
Excel 2003.

You can format them to appear as a negative number, but if you really want
them to be negative, you need to use a macro to react to the entry.


one approach you might use for an event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set rng = Me.Range("A1:Z26")
On Error Resume Next
Set rng1 = Intersect(Target, rng)
On Error GoTo 0
If Not rng1 Is Nothing Then
On Error GoTo ErrHandler
Application.EnableEvents = False
For Each cell In rng1
If IsNumeric(cell) Then
If cdbl(cell) = 0 Then
cell.Value = -1 * cell.Value
End If
End If
Next
ErrHandler:
Application.EnableEvents = True
End If

End Sub

Chip Pearson's page on events if unfamiliar:
http://www.cpearson.com/Excel/events.htm

--
Regards,
Tom Ogilvy


"Winfield" wrote in message
...
I am creating a spreadsheet that has a group of cells that is always a
negative number. I would like any number entered to default to negative
in
case the negative sign is forgotten when the number is entered. Can a
group
of cells be formated so they always appear as a negative nember? I am
using
Excel 2003.

If you just want them formatted as negative, use a format of

-General;-General

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Winfield" wrote in message
...
I am creating a spreadsheet that has a group of cells that is always a
negative number. I would like any number entered to default to negative
in
case the negative sign is forgotten when the number is entered. Can a
group
of cells be formated so they always appear as a negative nember? I am
using
Excel 2003.

"Tom Ogilvy" wrote:

You can format them to appear as a negative number, but if you really want
them to be negative, you need to use a macro to react to the entry.


one approach you might use for an event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set rng = Me.Range("A1:Z26")
On Error Resume Next
Set rng1 = Intersect(Target, rng)
On Error GoTo 0
If Not rng1 Is Nothing Then
On Error GoTo ErrHandler
Application.EnableEvents = False
For Each cell In rng1
If IsNumeric(cell) Then
If cdbl(cell) = 0 Then
cell.Value = -1 * cell.Value
End If
End If
Next
ErrHandler:
Application.EnableEvents = True
End If

End Sub

Chip Pearson's page on events if unfamiliar:
http://www.cpearson.com/Excel/events.htm

--
Regards,
Tom Ogilvy


"Winfield" wrote in message
...
I am creating a spreadsheet that has a group of cells that is always a
negative number. I would like any number entered to default to negative
in
case the negative sign is forgotten when the number is entered. Can a
group
of cells be formated so they always appear as a negative nember? I am
using
Excel 2003.


Thanks, I really want them to be negative. However, I have never created a macro and I am not sure where I would enter the information you gave me? I thank you in advance for your patience!

Take a look at David McRitchie's "Getting Started with Macros":

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In article ,
"Winfield" wrote:

Thanks, I really want them to be negative. However, I have never created a
macro and I am not sure where I would enter the information you gave me? I
thank you in advance for your patience!