Highlight cells with specific words from list of words

[pikesman]

Hi, I am working on a project and I need to search columns of data to see if a specific word/phrase is in there. I need a way to search a list of words and highlight cells that do not have any of those words. To be exact, I would like to search a number of columns for the phrases

Flower

Edible

Oil_Wax

Capsule_Oral

<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>

and highlight any cells that do not have any of those words, case sensitivity is crucial. Thank you for your help.

Quang

 

[FDibbins]

Hi Ford, i tried your method as well. It seems to work partially. I have the list of terms I want to search on O1:O3. I am searching column L. The problem is that it highlights words on the list and wrods not on the list.I used this formula "=--EXACT(L1,INDEX($O$1:$O$3,MATCH(L1,$L:$L,0)))=0" Did I make a mistake somewhere?

Yes you did. That INDEX and MATCH ranges need to be the same.
=--EXACT(L1,INDEX($O$1:$O$3,MATCH(L1,$O$1:$O$3,0)))=0

I think Joe needs a reply from you as well

 

[pikesman]

Hi Ford, Thank you for the response. I tried your formula and it did not work. However, when I change the 0 to 1 it worked. So I used =--EXACT(L1,INDEX($O$1:$O$3,MATCH(L1,$O$1:$O$3,0)))=1. When I use this formula, it highlighted the words on the list instead of the words not on the list. That works for me, I will just invert my thinking. I am interested in the logic of the situation though. The zero at the end of the line should match a false output and highlight it, but it did not do anything when I ran the formula. not sure why that is.

Quang

 

[FDibbins]

it looks at the entry in L1, searches for that EXACT match in the lookup range. =EXACT, on its own, will return either a TRUE or FALSE, so by putting -- in front, it forces TRUE to be 1 and FALSE to be 0

So testing for --EXACT=0 would fire if there was NOT an exact match

 

[pikesman]

Thank you Joe for all your help solving this question. The updated code works great. I attached the code below so that anyone with the same issue can look to it for guidance.

Sub pikesman()
Dim wrds As Variant, Cols As Range, V As Variant, R As Range, i As Long, j As Long, k As Long, Ct As Long
On Error Resume Next
Set Cols = Application.InputBox("Use your mouse to select columns (entire columns) to search", Type:=8)
On Error GoTo 0
If Cols Is Nothing Then Exit Sub
wrds = Array("Flower", "Edible", "Oil_Wax", "Capsule_Oral")
V = Intersect(Cols, ActiveSheet.UsedRange).Value
For i = 1 To UBound(V, 1)
    For j = 1 To UBound(V, 2)
        If V(i, j) <> "" Then
            Ct = 0
            For k = LBound(wrds) To UBound(wrds)
                If Not (V(i, j) Like "*" & wrds(k) & "*") Then
                    Ct = Ct + 1
                    If Ct = UBound(wrds) + 1 Then
                        If R Is Nothing Then
                            Set R = Intersect(Cols, ActiveSheet.UsedRange).Cells(i, j)
                        Else
                            Set R = Union(R, Intersect(Cols, ActiveSheet.UsedRange).Cells(i, j))
                        End If
                    End If
                Else
                    Exit For
                End If
            Next k
        End If
    Next j
Next i


If Not R Is Nothing Then
    Application.ScreenUpdating = False
    R.Interior.Color = vbYellow
    Application.ScreenUpdating = True
Else
    MsgBox "No cells found that don't have any keywords in them"
End If
End Sub

 

[pikesman]

Hi Ford, thank you for taking your time to answer my question. The formula works great for me, I just had to change the "0" at the end to "1." I do understand the --EXACT would produce a "0" if it did not match the word(s). The 0 from the EXACT would match the 0 at the end of the formula and fire the formatting. The confusion for me is that it did not highlight anything for me, but when I changed it the the 0 at the end of the formula to 1 it highlighted all matches. logically speaking the 1/0 at the end of the formula should just invert the selection, but it did not. Again works for me, I just look for non-highlighted cells instead of highlighted. I am just interested in why it did not work in the opposite direction.

 

[Rick Rothstein]

Thank you Joe for all your help solving this question. The updated code works great. I attached the code below so that anyone with the same issue can look to it for guidance.

Sub pikesman()
Dim wrds As Variant, Cols As Range, V As Variant, R As Range, i As Long, j As Long, k As Long, Ct As Long
On Error Resume Next
Set Cols = Application.InputBox("Use your mouse to select columns (entire columns) to search", Type:=8)
On Error GoTo 0
If Cols Is Nothing Then Exit Sub
wrds = Array("Flower", "Edible", "Oil_Wax", "Capsule_Oral")
V = Intersect(Cols, ActiveSheet.UsedRange).Value
For i = 1 To UBound(V, 1)
    For j = 1 To UBound(V, 2)
        If V(i, j) <> "" Then
            Ct = 0
            For k = LBound(wrds) To UBound(wrds)
                If Not (V(i, j) Like "*" & wrds(k) & "*") Then
                    Ct = Ct + 1
                    If Ct = UBound(wrds) + 1 Then
                        If R Is Nothing Then
                            Set R = Intersect(Cols, ActiveSheet.UsedRange).Cells(i, j)
                        Else
                            Set R = Union(R, Intersect(Cols, ActiveSheet.UsedRange).Cells(i, j))
                        End If
                    End If
                Else
                    Exit For
                End If
            Next k
        End If
    Next j
Next i


If Not R Is Nothing Then
    Application.ScreenUpdating = False
    R.Interior.Color = vbYellow
    Application.ScreenUpdating = True
Else
    MsgBox "No cells found that don't have any keywords in them"
End If
End Sub

Here is another, more compact way to write this macro (I think it will be faster as well)...

[table="width: 500"]
[tr]
	[td]Sub pikesman2()
  Dim Word As Variant, Cols As Range
  On Error Resume Next
  Set Cols = Application.InputBox("Use your mouse to select columns (entire columns) to search", Type:=8)
  On Error GoTo 0
  If Cols Is Nothing Then Exit Sub
  Application.ScreenUpdating = False
  Cols.SpecialCells(xlConstants).Interior.Color = vbYellow
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Interior.Color = xlNone
  For Each Word In Array("[COLOR="#0000FF"][B]Flower[/B][/COLOR]", "[COLOR="#0000FF"][B]Edible[/B][/COLOR]", "[COLOR="#0000FF"][B]Oil_Wax[/B][/COLOR]", "[COLOR="#0000FF"][B]Capsule_Oral[/B][/COLOR]")
    Cols.Replace Word, Word, xlPart, , True, , False, True
  Next
  Application.ReplaceFormat.Clear
End Sub[/td]
[/tr]
[/table]

 

[pikesman]

Hi Rick, thank you for the shortened version, it works great. As far as speed, they both go at near speed of light to me so...I cannot tell.

Thank you,
Quang