Conditional formatting

[Wombat_Assassin]

Ok first off, here is an image of what I need help with:

[IMG=http://img267.imageshack.us/img267/2039/excelproblem.png][/IMG]

Basically, the Rows with a red line at the start are when I buy an item, and the ones with blue lines at the start are when I sell an item. This is not in the image, but farther to the left, is a date column, each date has 2 Rows, a red and a blue.

I want to make it, where when I sell, it checks in the most recent BUY cell to check how much it was bought for, and if I made a loss, it will fill the SELL cell a certain colour (Red) and if I made a profit, it will fill it another colour (Green).

Now the problem is, I might buy something, and sell it on the same day, like the first E.G. in the image, or it might be a few days before I sell it. So it has to go back and check the most recent BUY cell that has data in it.

I realise this may be a bit hard to understand, but if you DO understand it, great!! If you don't, please feel free to ask as many questions as necessary, as I want to get this done ASAP, Thanks everyone!!

[Wombat_Assassin]

Any ideas yet people?

[6StringJazzer]

First, be aware that 53 minutes is not a long time for a post to await response here.

Second, if you have a workbook you can attach, that is always preferable to posting pictures or descriptions. You can attach them right here.

It is not at all clear what data your are recording for each transaction. You have, for example, three different numbers in the first row. What do these numbers mean? Are they for a single transaction, or three different transactions? What is the significance of the columns they are in? How can row 1 have "sell" data when there is no prior "buy" data?

To solve this you need to record a unique identifier for each item bought and sold, price, and quantity. I can't tell if any of your numbers correlate to any of that data.

Do you buy or sell multiple quantities or just one at a time?

If you buy an item and later buy another identical item, and then later sell one of them, do you calculate profit using a FIFO method, LIFO method, or identifying items uniquely?

[Wombat_Assassin]

Stock Market Test.xlsx

Here is the sheet you requested, it's for buying stocks in the stock market.
As you can probably tell from the top row, those are the companies in which I am buying the stocks, the left number in each company column is the quantity bought, the right number is the price per share, I have a bunch of calculators working in the background starting at AP24 I think, you can check them out if you wish.

I think that answers all the questions so far. Think you can work with it now?

[6StringJazzer]

It's much clearer what you're trying to do now. I need to digest this a little bit. How important is it to you to keep the data in this same format? Other formats might be easier to work with, for example keeping purchases in one column and sales in another, instead of by interleaved rows.

[Wombat_Assassin]

Ye, I would really love to keep it in this format.

[NBVC]

Maybe this will work?

Select B2:U23 and invoke Conditional Formatting|Manage Rules.

Delete any current rules.

Then apply New Rule.

Select Use formula to determine which cells to format and enter formula:

=AND(B$1<>"PPS",$V2="Sell",ISNUMBER(B2),(B2-LOOKUP(2,1/(($V$2:$V2="BUY")*(ISNUMBER(B$2:B2))),B$2:B2))<0)

then click Format and choose Red from the fill menu.

then click Ok.

Click New rule and repeat above with formula:

=AND(B$1<>"PPS",$V2="Sell",ISNUMBER(B2),(B2-LOOKUP(2,1/(($V$2:$V2="BUY")*(ISNUMBER(B$2:B2))),B$2:B2))>0)

and colour Green.

Does that work?

[Wombat_Assassin]

YES it works, thanks!! There's just one problem, on the left column is the amount of shares bought, so I don't really want it to be filled with red, if I buy 2000 shares, then sell 1000, because 1000 is less than 2000.

Would it be possible to make it so that, for example, the 1000 would change colour solely on the colour of the price next to it. So if I buy 2000 shares at 20 and then later on sell 1000 shares at 25, both 1000 and 25 would become green. Am I making this clear enough?

Apart from that it's perfectly fine, thanks alot dude.

[NBVC]

Can you do me a favour and fill out your previous sample workbook with some more sample data and manually colour the cells that need colouring and put some notes here and there to identify why the cell should be coloured in those ways.