Hi All
I'm using Excel 2007, I have a brilliant conditional formatting code that
applies conditional formatting to a column of data that contains 40 different
product names.

The code looks at strings in $C$95:$C$300 (user enters any text but always
includes the product name somewhere). When it finds the product name it
colours the cell for that product.

My problem is that new product codes will need be added to the list.
I'd like the user to be able to add new products to the list and the code
pick up those new products and include them in the conditional formatting.

Can the code use a list of product names for it's criteria instead of having
each product name specifically entered into the code?

e.g. (Current code)
For Each Cell In Range("$C$95:$C$300")
If Cell.Value Like "*MOL Summer Bundle C*" Then
'(can the above step lookup the next product name (somewhere within a
*string*) in the product list and use that as the criteria instead of having
to quote the actual name "*MOL Summer Bundle C*"?)

The "product name" list appears in the the current worksheet - range
$Z$74:$Z$114.

Current Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Range("$C$95:$C$300")
If Cell.Value Like "*MOL Summer Bundle C*" Then
Cell.Interior.Color = vbRed
ElseIf Cell.Value Like "*Autumn Bundle A*" Then
Cell.Interior.Color = vbBlue
ElseIf Cell.Value Like "*Gold*" Then
Cell.Interior.Color = vbGreen
End If
Next Cell
End Sub

I would also like to thank all the Microsoft experts for all the help they
provide in this discussion group. It is absolutely invaluable to novice
users like me who are trying to learn and automate manual tasks.
--
Cheers
BeSmart