Making a cell Negitive only

H

hw

Can this be done by formating or do I need a formula. I thought I may be able
to do this using the "IF" function but the examples I've seen only give me
text. I really just want certian cells to have Negitive numeric values only.
I am trying to create a general ledger so certian cells must be negitive to
balance.
Thanks! Ya'll Rock BTW
 
D

Dave Peterson

You could use another cell and a formula:

=-abs(a1)

Or you could use a worksheet event that looks for positive values in that range.

Just rightclick on the worksheet tab that should have this behavior. Select
View code and paste this in the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Cells.Count > 1 Then Exit Sub

If Intersect(Me.Range("a:a"), Target) Is Nothing Then
Exit Sub
End If

On Error GoTo ErrHandler:
If IsNumeric(.Value) Then
If .Value > 0 Then
Application.EnableEvents = False
.Value = -Abs(.Value)
Beep 'just to notice
End If
End If
End With

ErrHandler:
Application.EnableEvents = True
End Sub

I used all of column A. Modify it the way you like.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
R

Ron Rosenfeld

Can this be done by formating or do I need a formula. I thought I may be able
to do this using the "IF" function but the examples I've seen only give me
text. I really just want certian cells to have Negitive numeric values only.
I am trying to create a general ledger so certian cells must be negitive to
balance.
Thanks! Ya'll Rock BTW
Click to expand...

How do the numbers get into these cells?

If formula, please post the formula.

If by direct entry, you can either use Data Validation to ensure a negative
number is entered; or you could have an event driven macro which would convert
any positive number entered to a negative one.

Let us know, and a solution will be forthcoming.


--ron
 
B

bj

try <data><Validation><whole Number><less than> 0
select what you want the computer to door say when a positive number is
entered.
 
Top