Conditional Formatting VBA with formula to find string

[BeSmart]

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

 

[Peter T]

Not sure I quite follow all the details, but to simplify I put your 4 data
cells in A1:A4
I selected A1:A4
then added this conditional format
IsFormula
=ISERROR(FIND("Product One", A1))=FALSE
and added a format
When done CFs in A1 and A3 were triggered.
I could have gone on to add further CFs the same way

If "Product One" is in say cell F1 on the same sheet, could change "Product
One" to $F$1 in the formula

Regards,
Peter T

 

[fisch4bill]

Try some code similar to this:

Option Explicit
Sub Liminal()
Dim Cell As Range

For Each Cell In Range("$C$95:$C$300")
If Cell.Value Like "*Product A*" Then
Cell.Interior.Color = vbRed
ElseIf Cell.Value Like "*Product B*" Then
Cell.Interior.Color = vbBlue
ElseIf Cell.Value Like "*Product C*" Then
Cell.Interior.Color = vbGreen
End If
Next Cell

End Sub

Modify the product names to suit and add as many "ElseIf" sections as
needed, being sure to keep the * both before and after the string (and within
the double quotes) that corresponds to the product name. I performed a small
test and it worked fine.

HTH
Bill

 

[BeSmart]

Thanks Fisch - that works great, however we will have new products that will
need to be added and it would be better to do this via a list of product
names rather than constantly having to update the code.

Is there a way for the code to lookup each "product name" from a list (range
Z74:Z114 on the current worksheet) and searched for it in strings?

e.g.
ElseIf Cell.Value Like "*Product B*" Then

"*Product B*" needs to look at the next name in the list (Z74:Z114) &
search for that product name in the strings.

That way the users can add/change the product names as required without the
code having to be updated?