Highlight entire row when word is in cell

**ladydubs** · 05-12-2009, 11:24 AM

What I want to do is select all rows that contain the word conveyor in it. So far I have managed to select all the cells, I have also managed to select the entire row but one row at a time, I have even been able to turn all the cells a different color but I just want to highlight them for other formatting, copying, and several other things that need to be done. I have other documents this could be very useful in too so i want to make it a generic multi-line highlight rather than adding the formatting into the formula.

Can someone please help. Thank you in advance.

**sweep** · 05-12-2009, 11:51 AM

Hi,

I think this is what you're after. Obviously you can swap "selection" out for any range you need

Please Login or Register  to view this content.

**ladydubs** · 05-12-2009, 12:18 PM

Its giving me the following error:

Object variable or With block variable not set (Error 91)

**ladydubs** · 05-12-2009, 03:19 PM

Sheet 1 contains columns a through g, in column f there is descriptions of work done. When the word "conveyor" appears in column f, i would like that entire row highlighted. I would like for all the rows that"conveyor" appears in to be highlighted at once. After they are selected, I want to copy out the rows into a new sheet.

**contaminated** · 05-12-2009, 03:44 PM

I think u can use conditional formatting

Look attached file.

**ChemistB** · 05-12-2009, 03:48 PM

I tested it and it worked. Step 1 is to select your entire range (e.g. A1:G500) and then run the macro. Did you do that?

**martindwilson** · 05-12-2009, 03:58 PM

try

Please Login or Register  to view this content.

remember you must select the range before running macro

**ladydubs** · 05-12-2009, 04:04 PM

Ok, so i see the error but don't know how to fix it. I should have specified, my bad.

the cells that contain "conveyor" are not exclusive to the word "conveyor". It could say "Conveyor has stopped" or "Part is stuck in conveyor". The code works if "conveyor" is the only word in the cell not in a sentence.

**ChemistB** · 05-12-2009, 04:06 PM

You also might want to include (near the beginning of the macro) the line

Please Login or Register  to view this content.

if you are unsure as to whether the string you are looking for (conveyor) is actually in the range.

**ladydubs** · 05-12-2009, 04:09 PM

martindwilson, thank you, that worked great. Thank you everyone that helped me with this. I can utilize this for so many projects i have going on.

I appreciate it to the umpteenth degree.