Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting VBA with formula to find string
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting VBA with formula to find string
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 "BeSmart" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting VBA with formula to find string
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting VBA with formula to find string
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? -- Thank for your help BeSmart "fisch4bill" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formats to Find Words in Text String | Excel Worksheet Functions | |||
Way to find if conditional formatting triggered? | Excel Discussion (Misc queries) | |||
How to find cells with non-conditional formatting? | New Users to Excel | |||
String Comparison & Conditional Formatting | Excel Discussion (Misc queries) | |||
Conditional formatting for lowest number in a string | Excel Worksheet Functions |