Cell Formatting

[divapaxo]

Hi All

wonder if someone could help me

I have 2 columns A&B
In column A i have wording Buy or Sell & in column
B I have values ie 1111 & 2222

I want to put some sort of format in so that if Column A is Sell
it will change the value in column B to a negative and if it is a Buy
it will leave the value as Positive.

 

[Richard Schollar]

Hi

You can't change the value of a cell thru formatting alone. Can you provide specific examples of what values you wish to change to a negative and when. You could use a formula in an adjacent column along the lines of:

=IF(A2="Sell",-B2,B2)

 

[Lewiy]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Cells(Target.Row, 1) = "Sell" Then
    Cells(Target.Row, 2) = -Abs(Cells(Target.Row, 2))
    Else: Cells(Target.Row, 2) = Abs(Cells(Target.Row, 2))
End If
End Sub

Right click the worksheet tab and select "View Code" and paste this in the window that opens up.

 

[sibbles]

Hello
- Edit: Richard beat me to it

You should be able to do this with an simple if statement in column C and then multiply cell B with C

My first suggestion would be:

In cell C1: =if(A1="Sell";-1;1)
Then in cell B1 =1111*C1

There are most likely a gazillion other ways to do this but this one came to my mind

Regards
Atli

 

[divapaxo]

Thank you everyone that has worked a treat