Hi,
Please can I get some help....
I need to create a conditional format VBA in Excel 2007 (because I have many
conditions to include) and I don't know how to do it...

The Action Required:
If Product A appears in any text string in range($C$95:$C$300) then colour
that cell RED,
if Product B appears anywhere in a text string within range($C$95:$C$300)
then colour that cell BLUE,
if Product B appears anywhere in a text string within range($C$95:$C$300)
then colour that cell GREEN,
and so on through 41 products...

Data
- I have a list of about 41 Product names in range Z74:Z114.

- Data cells are in range($C$95:$C$300) which is named "Prod_name"
This list of data will quote the product name somewhere in the text string.


e.g.
Data
Cell C105 = "Special for Product One"
Cell C106 = "Product Six last offer"
Cell C107 = "Coming soon Product One plus more"
Cell C110 = "Today Product Three are in"

Product LIst
Product One
Product Two
Product Three
Product Four
Product Five
Product Six
etc.

I need cell C105 to colour fill RED
I need cell C106 to colour fill BLUE
I need cell C107 to colour fill RED (same as C105 because they're both
Product One)
I need cell C110 to colour fill GREEN
etc through the data range.
If no match is found, the data cell does not get filled.

I tried to use the Conditional Formatting "Case" functions but I couldn't
get it to accept a Range (to look at the product names), or a formula e.g.
SEARCH or MATCH.

Any help would be greatly appreciated.
Cheers
BeSmart