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" <[email protected]> wrote in message
news:[email protected]...
> 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
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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.