CODEWITHSUNDEEP
excelvba
F.Ymn
F.Ymn

Reputation: 13

Highlight a cell if there is a specific word in the cell's text

I want to red highlight all cells that contain the word "Color" and "Width".

With the code I have, I enter the cell's value. I want to enter the word "Color" and "Width".

Sub test()    

aaa = InputBox("Enter value 1:")
bbb = InputBox("Enter value 2:")

Dim myrange As Range
Set myrange = ThisWorkbook.Worksheets("Tabell").UsedRange

For Each cell In myrange.Cells

    If cell.Value = aaa Or cell.Value = bbb Then
        cell.Interior.Color = 255
    End If

Next

End Sub

enter image description here

Upvotes: 0

Views: 73

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

You can use Like.

edit - updated to allow for multiple terms to be entered (untested)

dim terms as new collection, term, deleteMe as boolean

do
    term = Trim(InputBox("Enter value (leave blank to end entry)"))
    if len(term)>0 then
        terms.add term
    else
        exit do
    end if
loop

if terms.count=0 then exit sub '<< no terms to check for

For Each cell In myrange.Cells

    deleteMe = true

    for each term in terms
        if not cell.value like term & "*" then
            deleteMe = false 
            exit for
        end if
    next term

    if deleteMe then cell.value = ""
next cell

Upvotes: 1

Related Questions