Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formatting code that looks up a list of criteria
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting 2 criteria in one row | Excel Discussion (Misc queries) | |||
Conditional formatting for many criteria | Excel Discussion (Misc queries) | |||
Conditional Formatting of 4 criteria | Excel Discussion (Misc queries) | |||
CONDITIONAL FORMATTING FOR MORE THAN 3 CRITERIA | Excel Discussion (Misc queries) | |||
need help with conditional formatting 3 criteria | Excel Worksheet Functions |