Looking to Highlight Certain Phrases

[Macro628]

Hello,

I've ran into a situation where I'm trying to highlight all instances for "pizza deal" the problem is that with the symbols in the word it doesn't highlight all the words such as "pizza deals, online pizza deals, etc." My goal is as long as it has the words "pizza" and "deal" it should highlight that particular cell. I thought maybe using vba could do the trick, but I ran into a dead end again.

Another option I was thinking about (that I created on the second tab) is having a list of the words I'm looking for such "pizza deal", "salad deal" and it would highlight all those instances including "online salad deals", "deals for pizza" etc. Any help on this would be appreciated...

Thanks

[mancubus]

perhaps something like this (for Sheet1)

=IFERROR(FIND("pizza deal",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"[",""),"]",""),"+",""))>0,FALSE) 

[p45cal]

or:
=ISNUMBER(SEARCH("pizza",B2)+SEARCH("deal",B2))
(in column C of Sheet1 of the attached)
or:
=ISNUMBER(SUMPRODUCT(SEARCH({"pizza","deal"},B2)))
(in column D of Sheet1 of the attached)
On Sheet1 of the attached I've used a variant of the second of these to conditionally format column B:
=ISNUMBER(SUMPRODUCT(SEARCH($H$2:$I$2,$B2)))

Here I've referred to cells H2:I2 (highlighted in green on the sheet). Change the values in these cells and the formatting changes in column B.

On sheet Option two is a vba offering. You're meant to highlight the cells with the phrases (I2:I3), then click the button.

[Paul_Hossler]

I'm not 100% sure on the requirement, but using VBA and Option 2 this marks the "Text" cells that have all words from any of the "Lookup" cells

Option Explicit
Sub MarkCells()
    Dim ws As Worksheet
    Dim rText As Range, rLookup As Range
    Dim aText() As Variant, aLookup() As Variant
    Dim iLookup As Long, iText As Long, iLookupPiece As Long
    
    Set ws = Worksheets("Option Two")
    Set rText = Range(ws.Cells(1, 1), ws.Cells(1, 1).End(xlDown))
    Set rLookup = Range(ws.Cells(1, 2), ws.Cells(1, 2).End(xlDown))
    
    aText = Application.WorksheetFunction.Transpose(rText)
    
    ReDim aLookup(1 To rLookup.Rows.Count)
    For iLookup = 1 To rLookup.Rows.Count
        aLookup(iLookup) = Split(rLookup.Cells(iLookup, 1).Value, " ")
    Next iLookup
    
    
    For iLookup = LBound(aLookup) + 1 To UBound(aLookup)
        For iText = LBound(aText) + 1 To UBound(aText)
            For iLookupPiece = LBound(aLookup(iLookup)) To UBound(aLookup(iLookup))
                If InStr(aText(iText), aLookup(iLookup)(iLookupPiece)) = 0 Then Exit For
                
                rText.Cells(iText, 1).Interior.Color = vbRed
    
            Next iLookupPiece
    
        Next iText
    Next iLookup
    
    Stop
End Sub

[Macro628]

Hello,

I just wanted to thank all those who helped with this problem. The Vba without conditional formatting and the one with conditional formatting both work brilliantly for the objectives.

Thanks again for looking at this.