I need to know how to make a column so that when you type a number into it,
it will become a negative number without you having to type the minus sign
yourself. The regular negative number format does not work and I cannot come
up with a custom one that works either. It must subtract when totaling the
columns.

Hooking the Change event will do it.

First, create a named range called "MyNegativeRange"

Then right-click the worksheet tab and select View Code.
Paste this code.

Private Sub Worksheet_Change(ByVal Target As Range)
Const cNegRangeName = "MyNegativeRange"
Dim rngTarget As Range, rng As Range

Set rngTarget = Intersect(Target, Range(cNegRangeName))
If Not rngTarget Is Nothing Then
Application.EnableEvents = False
For Each rng In rngTarget
If IsNumeric(rng.Value) Then rng.Value = -Abs(rng.Value)
Next
Application.EnableEvents = True
End If
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Kathy" wrote in message
...
I need to know how to make a column so that when you type a number into it,
it will become a negative number without you having to type the minus sign
yourself. The regular negative number format does not work and I cannot
come
up with a custom one that works either. It must subtract when totaling the
columns.

This seemed to work but when I changed the font color to red and brackets
around it a debug box keeps popping up. I have several debit and credit
columns and then of a total column for daily balance, monthly balance, and
balance forward. I need my credit columns to be the negatives in red and
brackets. How do I debug this? Thank you for all your help!



"Rob van Gelder" wrote:

Hooking the Change event will do it.

First, create a named range called "MyNegativeRange"

Then right-click the worksheet tab and select View Code.
Paste this code.

Private Sub Worksheet_Change(ByVal Target As Range)
Const cNegRangeName = "MyNegativeRange"
Dim rngTarget As Range, rng As Range

Set rngTarget = Intersect(Target, Range(cNegRangeName))
If Not rngTarget Is Nothing Then
Application.EnableEvents = False
For Each rng In rngTarget
If IsNumeric(rng.Value) Then rng.Value = -Abs(rng.Value)
Next
Application.EnableEvents = True
End If
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Kathy" wrote in message
...
I need to know how to make a column so that when you type a number into it,
it will become a negative number without you having to type the minus sign
yourself. The regular negative number format does not work and I cannot
come
up with a custom one that works either. It must subtract when totaling the
columns.

Could you please tell me the exact steps you go through to get the error?

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Kathy" wrote in message
...
This seemed to work but when I changed the font color to red and brackets
around it a debug box keeps popping up. I have several debit and credit
columns and then of a total column for daily balance, monthly balance,
and
balance forward. I need my credit columns to be the negatives in red and
brackets. How do I debug this? Thank you for all your help!



"Rob van Gelder" wrote:

Hooking the Change event will do it.

First, create a named range called "MyNegativeRange"

Then right-click the worksheet tab and select View Code.
Paste this code.

Private Sub Worksheet_Change(ByVal Target As Range)
Const cNegRangeName = "MyNegativeRange"
Dim rngTarget As Range, rng As Range

Set rngTarget = Intersect(Target, Range(cNegRangeName))
If Not rngTarget Is Nothing Then
Application.EnableEvents = False
For Each rng In rngTarget
If IsNumeric(rng.Value) Then rng.Value = -Abs(rng.Value)
Next
Application.EnableEvents = True
End If
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Kathy" wrote in message
...
I need to know how to make a column so that when you type a number into
it,
it will become a negative number without you having to type the minus
sign
yourself. The regular negative number format does not work and I
cannot
come
up with a custom one that works either. It must subtract when totaling
the
columns.

Why not simply format your credits column such that any entry is turned Red
and has brackets around the numbers using standard formatting options. Then
just adjust the calcs to subtract the credits as opposed to adding them.

Regards
Ken.................

"Kathy" wrote:

This seemed to work but when I changed the font color to red and brackets
around it a debug box keeps popping up. I have several debit and credit
columns and then of a total column for daily balance, monthly balance, and
balance forward. I need my credit columns to be the negatives in red and
brackets. How do I debug this? Thank you for all your help!



"Rob van Gelder" wrote:

Hooking the Change event will do it.

First, create a named range called "MyNegativeRange"

Then right-click the worksheet tab and select View Code.
Paste this code.

Private Sub Worksheet_Change(ByVal Target As Range)
Const cNegRangeName = "MyNegativeRange"
Dim rngTarget As Range, rng As Range

Set rngTarget = Intersect(Target, Range(cNegRangeName))
If Not rngTarget Is Nothing Then
Application.EnableEvents = False
For Each rng In rngTarget
If IsNumeric(rng.Value) Then rng.Value = -Abs(rng.Value)
Next
Application.EnableEvents = True
End If
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Kathy" wrote in message
...
I need to know how to make a column so that when you type a number into it,
it will become a negative number without you having to type the minus sign
yourself. The regular negative number format does not work and I cannot
come
up with a custom one that works either. It must subtract when totaling the
columns.

I made my range called MyNegativeRange. There I made my format to be red and
with brackets. I then applied this to my credit columns. Then I did the
code. I put a number in my column and the error box pops up to debug right
away. A sample of my columns is as follows:
Name Balance Monthly Daily 01-Dec-04
Debit Credit
John Doe 40.00 (10.00) (50.00) 10.00 (50.00)
John Smith 100.00 110.00 10.00 40.00 (30.00)
John No 200.00 190.00 (10.00) 40.00 (50.00)


"Rob van Gelder" wrote:

Could you please tell me the exact steps you go through to get the error?

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Kathy" wrote in message
...
This seemed to work but when I changed the font color to red and brackets
around it a debug box keeps popping up. I have several debit and credit
columns and then of a total column for daily balance, monthly balance,
and
balance forward. I need my credit columns to be the negatives in red and
brackets. How do I debug this? Thank you for all your help!



"Rob van Gelder" wrote:

Hooking the Change event will do it.

First, create a named range called "MyNegativeRange"

Then right-click the worksheet tab and select View Code.
Paste this code.

Private Sub Worksheet_Change(ByVal Target As Range)
Const cNegRangeName = "MyNegativeRange"
Dim rngTarget As Range, rng As Range

Set rngTarget = Intersect(Target, Range(cNegRangeName))
If Not rngTarget Is Nothing Then
Application.EnableEvents = False
For Each rng In rngTarget
If IsNumeric(rng.Value) Then rng.Value = -Abs(rng.Value)
Next
Application.EnableEvents = True
End If
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Kathy" wrote in message
...
I need to know how to make a column so that when you type a number into
it,
it will become a negative number without you having to type the minus
sign
yourself. The regular negative number format does not work and I
cannot
come
up with a custom one that works either. It must subtract when totaling
the
columns.

Indeed - thanks Ken.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Ken Wright" wrote in message
...
Why not simply format your credits column such that any entry is turned
Red
and has brackets around the numbers using standard formatting options.
Then
just adjust the calcs to subtract the credits as opposed to adding them.

Regards
Ken.................

"Kathy" wrote:

This seemed to work but when I changed the font color to red and brackets
around it a debug box keeps popping up. I have several debit and credit
columns and then of a total column for daily balance, monthly balance,
and
balance forward. I need my credit columns to be the negatives in red and
brackets. How do I debug this? Thank you for all your help!



"Rob van Gelder" wrote:

Hooking the Change event will do it.

First, create a named range called "MyNegativeRange"

Then right-click the worksheet tab and select View Code.
Paste this code.

Private Sub Worksheet_Change(ByVal Target As Range)
Const cNegRangeName = "MyNegativeRange"
Dim rngTarget As Range, rng As Range

Set rngTarget = Intersect(Target, Range(cNegRangeName))
If Not rngTarget Is Nothing Then
Application.EnableEvents = False
For Each rng In rngTarget
If IsNumeric(rng.Value) Then rng.Value = -Abs(rng.Value)
Next
Application.EnableEvents = True
End If
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Kathy" wrote in message
...
I need to know how to make a column so that when you type a number
into it,
it will become a negative number without you having to type the minus
sign
yourself. The regular negative number format does not work and I
cannot
come
up with a custom one that works either. It must subtract when
totaling the
columns.