Search and mark Vba - Excel

I have done a VBA code.

The purpose of this is that I should be able to enter the word "Color" and "Width", for example. Then, the program will red highlight all cells that contain the word Color and Width.

With the VBA code I have today it doesn't work. Without having to enter the whole cell's value. But I don't want to. I just want to be able to enter the word "Color" and "Width".

Can anyone help me please?

I have attached my Excel file.

Thanks!

MY VBA CODE IS :

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

2 answers

  • answered 2019-03-13 19:30 Tim Williams

    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
    

  • answered 2019-03-14 09:55 F.Ymn

    please i need help with another thing:

    My code looks at the forgotten seen now:

    Sub test()

    aaa = InputBox("Which object information do you want to display:")

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

    For Each cell In myrange.Cells

    If Not (cell.Value Like aaa & "*") Then cell.Value = "" End If

    Next

    End Sub


    But the problem with it is that you can write in once what it should keep. That is, the user can only enter once and then it will delete everything except what it writes. But I want the user to be able to type in several times before removing everything else. That is, I want the user to write, for example: Objects, Traps AND SEN if it writes the word END then the program should end up asking him, If he wants to write several things.

    So I want the user to be able to write several things and then when he is finished writing END and then it stops asking him.