negative numbers

[Guest]

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]

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

 

[Bob Phillips]

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)

 

[Guest]

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





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!

 

[JE McGimpsey]

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

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