Conditional format

[motel113]

i have a list
a b
5000 c
5000 d
and i want to make the value of colum a to be negative if the value of colum
b is equal to c, i just can't figure it out. please help

 

[Marcelo]

if you have a number on the column a I will sugest you to create a new column
between A and B (new column B) and you will have

A1 = 5000
B1 = =if(c1=d1,-a1,a1)
C1 = a
D1 = a

hth

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"motel113" escreveu:

 

[Gord Dibben]

You cannot do that with Conditional Format.

Changing a format does not change the underlying value of the cell.

You could use a helper column and enter a formula.........

=IF(B1="c",A1*-1,"not equal c") copy down column C

This won't change the values in column A to negative but will give you a
negative in column C

OR you could use event code to do it in place assuming you have the letters in
column B and are entering numbers in Column A

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 Target.Offset(0, 1).Value = "c" Then
With Target
.Value = .Value * -1
End With
End If
End If
enditall:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[motel113]

thank you for your help that worked perfectly

You cannot do that with Conditional Format.

Changing a format does not change the underlying value of the cell.

You could use a helper column and enter a formula.........

=IF(B1="c",A1*-1,"not equal c") copy down column C

This won't change the values in column A to negative but will give you a
negative in column C

OR you could use event code to do it in place assuming you have the letters in
column B and are entering numbers in Column A

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 Target.Offset(0, 1).Value = "c" Then
With Target
.Value = .Value * -1
End With
End If
End If
enditall:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP