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

 

[JoeMo]

Welcome to the forum!

This is lightly tested so try it on a copy of your worksheet. Any non-blank cell in the columns you select when prompted that contains none oof your keywords (case sensitive) will be highlighted with a yellow fill.

Sub pikesman()
Dim wrds As Variant, Cols As Range, V As Variant, Adrs As String, 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 Adrs = Adrs & "," & Intersect(Cols, ActiveSheet.UsedRange).Cells(i, j).Address(0, 0)
                Else
                    Exit For
                End If
            Next k
        End If
    Next j
Next i

If Adrs <> "" Then
    Application.ScreenUpdating = False
    Range(Right(Adrs, Len(Adrs) - 1)).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 Joe, Thank you for the quick reply. I tried the code but it came back as a error 400. I am running excel 2013. I cant tell exactly where the problem was, but it occurred after I selected the columns.

Thank you

 

[JoeMo]

Hi Joe, Thank you for the quick reply. I tried the code but it came back as a error 400. I am running excel 2013. I cant tell exactly where the problem was, but it occurred after I selected the columns.

Thank you

You can tell where the error is by selecting Debug when it occurs. Tell me what the error message says and what line is highlighted when the message appears. You need to select contiguous columns. If there are non-contiguous columns you want to search, run the macro on them one at a time.

 

[FDibbins]

Another, non-VBA approach.

1. Make up a list of your terms (I used J1:J4)
2. go into Conditional Formatting
3. highlight the range you want to apply the conditional formatting to
4. on the home tab, styles, select CF
5. select new rule, select use formula
6. enter =--EXACT(C1,INDEX($J$1:$J$4,MATCH(C1,$J$1:$J$4,0)))=0 format Fill your color
where C1 would be 1 of the cells you are

 

[pikesman]

Hi Joe, I am sorry I am a bit new to VBA. I tried going through line be line with F8 but it did not pop up any errors. Is there a proper way to debug the code? Is it possible to send you/link the excel file I am using to test this code?

 

[JoeMo]

Hi Joe, I am sorry I am a bit new to VBA. I tried going through line be line with F8 but it did not pop up any errors. Is there a proper way to debug the code? Is it possible to send you/link the excel file I am using to test this code?

If it didn't pop up any errors, what did it do? Are you selecting entire, contiguous columns? If you PM me, I will send you an email address so you can send me your test file.

 

[pikesman]

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?

 

[JoeMo]

If it didn't pop up any errors, what did it do? Are you selecting entire, contiguous columns? If you PM me, I will send you an email address so you can send me your test file.

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?

Having spent some time to provide you with a solution, I'd appreciate a reply to my questions. When I tested the code I posted, it worked fine for me so I'd like to understand why it doesn't work for you.

 

[pikesman]

Hi Joe, I sent you a PM to request and email address. I mean no disrespect, I was just trying all possible options. I don't know if the PM was delivered to you or not. I tried debugging it but I could not find reason why it gave an error 400.