switching values from positive to negative
 

Is it possible to automatically switch positive values to negative
values to entries in columns v to ir just by entering a 1 in either
column q or r on that same row. Conditional formatting?
Thanks in advance, Lob

Lob,

You would need to use the worksheet's change event. For example, if you
enter a 1 into column Q, the code below will change all negatives to
positives in column V to Z. It also allows you to undo your changes by
changing the 1 back to 0.

Copy the code below, right click on the sheet tab, select "View Code" and
paste the code in the window that appears.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range

If Target.Column < 17 Then Exit Sub
Application.EnableEvents = False
If Target.Value = 1 Then
For Each myCell In Range("V" & Target.Row).Resize(1, 5)
If myCell.Value < 0 Then myCell.Formula = _
"=-(" & myCell.Formula & ")"
Next myCell
End If
If Target.Value = 0 Then
For Each myCell In Range("V" & Target.Row).Resize(1, 5)
If Left(myCell.Formula, 3) = "=-(" Then myCell.Formula = _
Mid(myCell.Formula, 4, Len(myCell.Formula) - 4)
Next myCell
End If

Application.EnableEvents = True
End Sub



"lob" wrote in message
oups.com...
Is it possible to automatically switch positive values to negative
values to entries in columns v to ir just by entering a 1 in either
column q or r on that same row. Conditional formatting?
Thanks in advance, Lob

<Smack forehead

Of course, my code as written changes negative values to positive, not
positive to negative.

Simply change

If myCell.Value < 0 Then myCell.Formula = _

to

If myCell.Value 0 Then myCell.Formula = _

Sorry about that,
Bernie
MS Excel MVP

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Lob,

You would need to use the worksheet's change event. For example, if you
enter a 1 into column Q, the code below will change all negatives to
positives in column V to Z. It also allows you to undo your changes by
changing the 1 back to 0.

Copy the code below, right click on the sheet tab, select "View Code" and
paste the code in the window that appears.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range

If Target.Column < 17 Then Exit Sub
Application.EnableEvents = False
If Target.Value = 1 Then
For Each myCell In Range("V" & Target.Row).Resize(1, 5)
If myCell.Value < 0 Then myCell.Formula = _
"=-(" & myCell.Formula & ")"
Next myCell
End If
If Target.Value = 0 Then
For Each myCell In Range("V" & Target.Row).Resize(1, 5)
If Left(myCell.Formula, 3) = "=-(" Then myCell.Formula = _
Mid(myCell.Formula, 4, Len(myCell.Formula) - 4)
Next myCell
End If

Application.EnableEvents = True
End Sub



"lob" wrote in message
oups.com...
Is it possible to automatically switch positive values to negative
values to entries in columns v to ir just by entering a 1 in either
column q or r on that same row. Conditional formatting?
Thanks in advance, Lob

Hey Bernie
This works great! Thanks.
I have another spreadsheet that I would like to accomplish a similar
task.
V1=SUM(V23:V200) and this is copied through to IR1.
Is there a code that could be entered to sum only the cells for each
perticular column have a "D" in cell Q for that row,
subtract (create a negative value) to all the cells for each perticular
column have a "P" in cell Q for that row,
and skip (create a neutral value) the sum on the cells for each
perticular column have a "O" in cell Q for that row.
I tried playing with the original code you sent me, but was
unsuccesfull.
Thanks very much for your help, you have opened the door for me to
learn more.
Lob

Lob,

You wouldn't need to use code. For example, the equation

=SUMIF($Q$23:$Q$200,"D",V23:V200)- SUMIF($Q$23:$Q$200,"P",V23:V200)

should add all the values in column V where the corresponding row in column
Q is "D", then subtract any whose corresponding value is "P"

HTH,
Bernie
MS Excel MVP


"lob" wrote in message
oups.com...
Hey Bernie
This works great! Thanks.
I have another spreadsheet that I would like to accomplish a similar
task.
V1=SUM(V23:V200) and this is copied through to IR1.
Is there a code that could be entered to sum only the cells for each
perticular column have a "D" in cell Q for that row,
subtract (create a negative value) to all the cells for each perticular
column have a "P" in cell Q for that row,
and skip (create a neutral value) the sum on the cells for each
perticular column have a "O" in cell Q for that row.
I tried playing with the original code you sent me, but was
unsuccesfull.
Thanks very much for your help, you have opened the door for me to
learn more.
Lob

Even Better. This one I understand. Thanks for your help.