Thread
:
Formating Cells as Negative
View Single Post
#
11
Posted to microsoft.public.excel.misc
Sandy Mann
external usenet poster
Posts: 2,345
Formating Cells as Negative
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
Replace @mailinator.com with @tiscali.co.uk
"Willing to learn" wrote in
message ...
What if I want different rows and colums, for instance:
C12:K20
Regards
"Sandy Mann" wrote:
It depends on what it is that you want to do.
So that means that whenever I entered a number in those cells, my
numbers are going to be -
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
Replace @mailinator.com with @tiscali.co.uk
"Willing to learn" wrote in
message ...
so how should I fix it?
Advice please
"Sandy Mann" wrote:
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
Replace @mailinator.com with @tiscali.co.uk
"Willing to learn" wrote in
message ...
It worked!
Thanks
" wrote:
You can try this:
Select C1:C20
Format Cells
Number
Custom--
-#,###,###.00
This should do it.
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 -
Reply With Quote
Sandy Mann
View Public Profile
Find all posts by Sandy Mann