Key word contains_Cell Highlight

[vijanand1279]

Hi,

I have an excel file attached with two sheets “Master sheet” and “Key words”. I am looking at a macro to highlight the cells in the master sheet where the cells containing the key words from the key words tab. Also, in column C it should give me the key word from which the cell got highlighted.

For eg: The key word "ABUSE" is the first key word in tab two. Similarly I am looking for a macro to highlight all the rows when the key word found in tab two. Column C "ABUSE"

Another example: Key word in tab two "UNITED STATES".
Column C "UNITED STATES"

Regards,
Vijay

[watersev]

hi vijanand1279, please check attachment, press Run.

The code makes case-insensitive search through the whole keywords list for each item.

What is the point of colour-highlighting?

[Andrew-R]

Does it have to be a macro?

The attached sheet uses a formula and conditional formatting.

[martindwilson]

this is doable without code just a function and conditional formatting

[vijanand1279]

Thank you so much. This will solve my purpose.

[martindwilson]

ah cat skinning!

[vijanand1279]

Ooops I had to open the thread again....When I was looking into all the options through macro or the conditional formatting....It misses few that I looked for. Is it possible to find the result with the key words containing the text.
For eg. if we search for the key work "tax" then it should pull the rows with "TAXTIME", TAX.COM etc. I did this through the text filter containing. Also through the conditional formatting method did not show the second option. For eg. MORAINE VALLEY COMMUNITY COLLEGE is having both the key words "COMMUNITY" and "COLLEGE".

Please help

[watersev]

as to macro solution, please check attachment

[vijanand1279]

It looks to be good. I tried to check for the key word containing "FOUNDATION" and in our macro it did not pull out the rows where it did not have the complete key word as "FOUNDATION". The master sheet has FOUNDA, FOUNDATI, FOUNDATIO and these rows did not get picked up once I run the macro.

Could you look into this.

[watersev]

if keyword is "FOUNDATION" it means the code will look for exact match and not its part or variances

[vijanand1279]

Yes. the key word is "FOUNDATION" but the text is partial in the master sheet...Is there a way to identify the partial text containing values too.

[watersev]

may be sort of that