I have to process a spreadsheet everyday from a large e-tailer where certain items are supplied by different people, for instance David might sell product1, product3, product7, James would sell product2, product5 and product9, and Mike would sell product4, product6, and product8.

I'm using Excel 2003.

What I'd like to do is to apply a CF to the sheet so that if, for example, Col A contains product1 or product3 or product7, then highlight the cell in yellow and so on, then I can sort by color (I have the function for that). At the moment I have to manually go through the sheet and colour the cells in according to manufacturer which is a real PITA. The problem is this : If I set CF up to colour cells for 'product1', but the text in the cell is 'product1 XX2ZZ', then it is ignored. I need to be able to highlight any cell that contains the text 'product1' etc.

The word is always the first word in the cell if this helps..
Can anyone advise?

Thanks loads...

use wildcard in countif and greater than 0 in conditional formating function

=AND(COUNTIF(A1,"product1*"))0

all the best

similarly for other product you can include + (plus) count if functions in excel 2003, if you are using excel 2007, there is multiple criteria supported function called "COUNTIFs"

cheers

Thanks for that, I'll give it a shot tomorrow

Regards
Andy