Formating Cells as Negative

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
Would you guys please help me out?
I would like to cells's C01:C20 do negatives numbers.
So that means that whenever I entered a number in those cells, my numbers
are going to be -
How can I do it?

Regards.
 
You can try this:

Select C1:C20
Format Cells
Number
Custom-->
-#,###,###.00

This should do it.
 
One note of warning, formatting will make a number LOOK like a negative
number but the value held in the cell will still be positive.

Try entering 123, (it will show as -123 of course), in C1 then -1 in a cell
that has not been formatted - say D1. Now enter in D2 enter =C1*D1 you
will get -123 whereas if C1 had really been negative you would have had 123
returned.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
so how should I fix it?

Advice please

Sandy Mann said:
One note of warning, formatting will make a number LOOK like a negative
number but the value held in the cell will still be positive.

Try entering 123, (it will show as -123 of course), in C1 then -1 in a cell
that has not been formatted - say D1. Now enter in D2 enter =C1*D1 you
will get -123 whereas if C1 had really been negative you would have had 123
returned.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Click to expand...
 
It depends on what it is that you want to do.

If by that you mean that you are manually entering the data then surely it
is just as simple to add the - as you type.

If you mean when you paste data into the range then you could add an event
Macro to change the sign:

Right-click on the sheet tab and select View code and then paste the
following code into the sheet module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column <> 3 Then Exit Sub
If Target.Row > 20 Then Exit Sub
Application.EnableEvents = False
Target.Value = Target.Value * -1
Application.EnableEvents = True
End Sub

This will change all positive values typed of pasted into C1:C20 into
positive values and all Negative values into positive ones
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Yes! it worked!
thank you a lot!

Regards.

Sandy Mann said:
It depends on what it is that you want to do.


If by that you mean that you are manually entering the data then surely it
is just as simple to add the - as you type.

If you mean when you paste data into the range then you could add an event
Macro to change the sign:

Right-click on the sheet tab and select View code and then paste the
following code into the sheet module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column <> 3 Then Exit Sub
If Target.Row > 20 Then Exit Sub
Application.EnableEvents = False
Target.Value = Target.Value * -1
Application.EnableEvents = True
End Sub

This will change all positive values typed of pasted into C1:C20 into
positive values and all Negative values into positive ones
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Click to expand...
 
You're very welcome, thanks for the feedback.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
The range is selected by the:

If Target.Column <> 3 Then Exit Sub
If Target.Row > 20 Then Exit Sub

Delete these two rows and change it to:

If Intersect(Target, Range("C12:K20")) Is Nothing Then Exit Sub

to give:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("C12:K20")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = Target.Value * -1
Application.EnableEvents = True
End Sub

Another warning: VBA is not part of XL, it is kind of "stuck on the side of
it" so unlike formuals which will automatically adjust when you change the
environment by, say, adding a column, VBA will not. If you add a new Column
B and you now want the negatives in the Range D12:L20 the code will still
use the original range until you alter it.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
message you're
the best!

No I'm not.

Actually theIntersect method is a much better way of setting the range but I
learned VBA on XL95 and the company that I worked for only upgraded to
XL2002 shorty before I retired so I still have bad habits. That is why I
hang out in the NG's - I want you guys to teach me good habits!

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
I inadvertently used the same sheet I tested the code gave you to work out
another answer and it failed because I entered text. Alter the code to:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("C12:K20")) Is Nothing Then Exit Sub
If Not Target.IsNumeric Then Exit Sub
Application.EnableEvents = False
Target.Value = Target.Value * -1
Application.EnableEvents = True
End Sub

To stop it blowing up.

Not extensive error handling but at least it will let you put text in the
same area.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Thanks a lot.

Sandy Mann said:
I inadvertently used the same sheet I tested the code gave you to work out
another answer and it failed because I entered text. Alter the code to:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("C12:K20")) Is Nothing Then Exit Sub
If Not Target.IsNumeric Then Exit Sub
Application.EnableEvents = False
Target.Value = Target.Value * -1
Application.EnableEvents = True
End Sub

To stop it blowing up.

Not extensive error handling but at least it will let you put text in the
same area.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Click to expand...
 
Back
Top