Cell Formatting

divapaxo

Board Regular
Joined
Feb 4, 2005
Messages
110
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.

:confused:
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Try this:
Code:
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

New Member
Joined
Jan 10, 2006
Messages
18
Hello
- Edit: Richard beat me to it :biggrin:

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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,536
Messages
5,596,748
Members
414,095
Latest member
George53

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top