Find and highlight cells in workbook that is NOT an exact match to the cell content

Hi,

I am trying to run a macro that will search for a word/words that are not an exact match to the target cell and then highlight the cell. I have a demo attached that runs a macro with an input box that works well but only with an exact match.

For example: I would like to search for "list" but any cell containing "list" will be highlighted e.g. "Check list" or "Quality List" or even "holistic" etc.

Would anyone be able to help?

Thanks

Andy

in your code, just change

LookAt:=xlWhole

to

LookAt:=xlPart

If your code works for the exact match, to do the same for partial match,
Change

xlWhole

INTO

xlPart

Thanks heaps for that millz & AB33, that works a treat!

The only problem I have now is that there is no option to find the next cell containing the search criteria (similar to normal "Find Next" function). Is there anyway to add this in?

Thanks

This is what was recorded by Excel when I made it find for "1" and clicking Find Next 4 times.

    Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Cells.FindNext(After:=ActiveCell).Activate

Thanks for that millz; this only looks for "1" though. Are you able to show how to incorporate the code to "Find" and "Find Next" for the search value?

I don't know where do you need to add that in

Hi Andy,
This is not exactly you want but hope you like it.

Regards,
tt3

Thanks for all your input everyone, it's much appreciated.

Robert - Thanks for the code but I get an error message at the "varSearchFor = Me.txtValue" point it seems. Apologies but I am extremely new to VBA and so am unable to read the code - I may be missing the obvious. Any help would be great.

Thanks

Hi Andy,
Try this one.

Regards,
tt3

Thanks for the input again but I think I may not have explained exactly what I am after. Below is a breakdown of what I would ideally like:

1) Search for a partial match via an input box
2) For the search to find the first occurrence that includes the search text, highlight the cell and move to the relevant worksheet (there are multiple worksheets that need to be searched)
3) If the first match is not the correct one I would like a method that will jump to the next cell in the workbook that contains the search text (similar to the "Find Next" button through the Find & Replace tool already incorporated in Excel)

Although the previous macros highlight all the cells they do not take the user to the correct sheet and so the user would have to search through every tab and scroll down each page to find the highlighted cells. Also, all occurrences are highlighted simultaneously instead of one at a time.

This may be a little complex and I am thinking that maybe I will just have to use the Find & Replace and live with the cells not being highlighted. However, if anyone thinks there is a way to solve the issue any further feedback would be greatly appreciated.

Thanks