Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional format
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional format
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: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional format
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 On Tue, 10 Jun 2008 11:17:00 -0700, motel113 wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional format
thank you for your help that worked perfectly
"Gord Dibben" wrote: 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 On Tue, 10 Jun 2008 11:17:00 -0700, motel113 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
New Conditional Format Overriding Previous Conditional Format | Excel Discussion (Misc queries) | |||
How to create a conditional format that changes the number format | Excel Discussion (Misc queries) | |||
Conditional Format - Format Transfer To Chart | Excel Discussion (Misc queries) | |||
copy conditional format to regular format | Setting up and Configuration of Excel | |||
copy conditional format as ACTUAL format | Excel Discussion (Misc queries) |