Ok, try this out; The code assumes (and depends on) you setting up the color
you want to see for each product name in the list found in $Z$74:(whatever
the bottommost cell is in column Z). In other words, if you want the "MOL
Summer Bundle C" to appear in red in the range of $C$95:$C$300, then when you
enter the name of the product in the 'Z' list, assign a red color to that
cell. This allows you to pick unique colors for each product line as you
enter the name in the list, and greatly simplifies the code. I tried it out
as a stand-alone and it works for me. I made the code more versatile by not
hardcoding the bottom cell of either range.

'Code starts:
Sub Stitution()
Dim Cell As Integer
Dim Z As Integer

For Z = 74 To Cells(Rows.Count, "Z").End(xlUp).Row
For Cell = 95 To Cells(Rows.Count, "C").End(xlUp).Row
If Cells(Cell, "C").Value Like "*" & Range("Z" & Z).Value & "*" Then
Cells(Cell, "C").Interior.Color = Range("Z" & Z).Interior.Color
End If
Next Cell
Next Z

End Sub
'Code ends

HTH
Bill

"BeSmart" wrote:

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