Format cell to be negative

  • Thread starter Thread starter Naas
  • Start date Start date
N

Naas

how can you format a cell so that all numbers entered
will always be a negative number
 
Hi
could only be done with VBA (using an event procedure). Try the
following 8put this macro in your worksheet module - not in a standard
module):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" and isnumeric(.value) Then
Application.EnableEvents = False
if .value > 0 then
.value = -.value
end if
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub
 
Hi

Another option is to use Data Validation. This can be set to flag whenever a
positive number is input. If you set the Allow box to Custom and the formula
to =A1<0 (for cell A1)
This does not stop people pasting in values above 0, however.

Andy.
 
Hi!

The function abs() returns the positive value of whatever is entered
Therefore =-abs(A1) in B1 would ensure that, if a number were entere
in A1, the result in B1 would be negative but of the same (absolute
size as A1 (zero excluded, of course).

Al
 
Back
Top