About Us

Willing to learn

Thanks a lot.

"Sandy Mann" wrote:

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

Replace @mailinator.com with @tiscali.co.uk

"Sandy Mann" wrote in message
...
Willing to learn" wrote in
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

Replace @mailinator.com with @tiscali.co.uk

"Willing to learn" wrote in
message ...
you're the best!
thank you for your help.

Regards

"Sandy Mann" wrote:

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 -

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
conditional formating cells i Excel based on other cells values	Elias Petursson	Excel Worksheet Functions	3	May 23rd 06 06:45 PM
formating of certain cells not possible!	Marty	Excel Discussion (Misc queries)	1	May 23rd 06 04:19 PM
Formating cells to display negative percentages in red fon...	Fast fred	Excel Discussion (Misc queries)	3	March 10th 06 11:59 PM
formating negative percentages	Jo	Excel Discussion (Misc queries)	2	April 22nd 05 10:08 PM
formating negative numbers	foxint	Excel Discussion (Misc queries)	4	April 13th 05 05:02 PM

Menu

About Us