Change dollar amount to a negative if another colum has a "P"
 

I have numbers in a colum that I want to change to a negative(PAID) amount if
there is a "P" in the next column. I need to be able to enter numbers so I
cannot place a formula in the numbers column.

Change dollar amount to a negative if another colum has a "P"
 

Marvin

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
N = Target.Row
If Excel.Range("A" & N).Value < "" _
And Excel.Range("B" & N).Value = "P" Then
Excel.Range("A" & N).Value = Excel.Range("A" & N).Value * -1
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Event Code entered into the sheet module.

Will change the numbers to negative as you enter them in column A if column B
has a "P".

Right-click on sheet tab and "View Code".

Paste into that module.


Gord Dibben Excel MVP

On Mon, 26 Sep 2005 16:49:05 -0700, Marvin
wrote:

I have numbers in a colum that I want to change to a negative(PAID) amount if
there is a "P" in the next column. I need to be able to enter numbers so I
cannot place a formula in the numbers column.

Change dollar amount to a negative if another colum has a "P"
 

you might try the following and then make adjustments as needed:

If ALL of the numbers are currently +, the following will find the "P's" and
change the numbers to neg. Warning: if you run this again it will again
reverse the signs.
Adjust accordingly.

Sub FindPmts()
Dim Amts As Range
Dim oCell As Range

Set Amts = Range("A1:A100")
For Each oCell In Amts
If UCase(oCell.Offset(0, 1)) = "P" Then
oCell = oCell * -1
End If
Next
End Sub

"Marvin" wrote:

I have numbers in a colum that I want to change to a negative(PAID) amount if
there is a "P" in the next column. I need to be able to enter numbers so I
cannot place a formula in the numbers column.