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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formatting code that looks up a list of criteria
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formatting code that looks up a list of criteria
WOW!!!!
Bill - that is amazing!!! Exactly what I needed!!!! Thank you thank you thank you!!!! I never stop being amazed at what can be done in Excel and how much you guys know! -- Thanks HEAPS for your help BeSmart "fisch4bill" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formatting code that looks up a list of criteria
Glad to be able to help - I'm less than a year into Excel programming, and
I've received MUCH help from this forum, so it's good to be able to give back. Bill "BeSmart" wrote: WOW!!!! Bill - that is amazing!!! Exactly what I needed!!!! Thank you thank you thank you!!!! I never stop being amazed at what can be done in Excel and how much you guys know! -- Thanks HEAPS for your help BeSmart "fisch4bill" wrote: 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 |
Reply |
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 |