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.