Delete Rows based on content

**fmluder93** · 01-05-2009, 06:56 PM

Can a macro be used to:

1. Delete rows that contain certain text in a worksheet ?
2. Highlight a cell a colur based on a response ??

I have attached an example of what i mean .....

In the Audit Protocol worksheet is the main information - which contains questions and answers .... If a yes, n/a or no is selected then a response is automatically generated in the observations column (thanks to SHG for helping me with the formula for this) ..... How do I then get the cell to change to red if a NO response only is selected ????

Then the information from the Audit Protocol worksheet (the observation column and number column) are copied across to the worksheet named Action List (this is done just by the copy function) .... How do I go about deleting rows that contain "no action required" - as these are not needed for the report to be generated ?????

Any help is muchly appreciated ..... Cheers

**stanleydgromjr** · 01-05-2009, 07:15 PM

fmluder93,

To attach a file - scroll down and see "Manage Attachments".

Have a great day,
Stan

**fmluder93** · 01-05-2009, 07:53 PM

hhhm - thought i had done that ... sometimes it doesnt pick up - i should have double checked !!!! ... lets try again !!!

**shg** · 01-05-2009, 08:07 PM

Assuming that the actions to be highlighted red can be determined solely by text of the action (e.g., Do not use equipment, Works to cease, and Do not proceed are red in any action cell), then you can use a list of 'red' phrases and conditional formatting:

Formula is: =ISNUMBER(MATCH(Me, RedPhraseList, 0)), where Me is the address of the cell.

**fmluder93** · 01-05-2009, 08:47 PM

you will have to pardon my stupidness - but do i need to create a redphraselist ????? and does it matter that the content where the cell is to be highlighted red already has conditional formatting in it (from the action list) ....

**shg** · 01-05-2009, 08:55 PM

but do i need to create a redphraselist

Yes. Put the phrases that should be red in an some out of the way place (same sheet or another sheet). Then on the page where the Q&A (not necessarily where the list of phrases is), do Insert > Name > Define, RedPhraseList (or whatever), put the cursor in the Refers to box, then select the range where the phrase list is.

**fmluder93** · 01-05-2009, 10:04 PM

thanks - i thought this may be the case ... ill have a go and see what happens !!!! fingers crossed .. cheers

**fmluder93** · 01-06-2009, 08:55 PM

ok - i had a go at putting this conditional formatting in - and now the cell comes up with a 0 .... I created the red phrase list ok - and then added the conditional formatting to the cell (along with the conditional formatting already in the cell) - and it just comes back with the 0 ....

maybe i have the conditional formatting all wrong and its probably a really obvious error on my part ..... so basically the cell has that if a yes, no or n/a is selected then a reponse is automatically generated (the blue conditional formatting below) and then i have tried to put that if the no response is chosen then the cell is to be highlighted red (the red conditional formatting below) .... where have i gone wrong ????

=IF(C15="","",VLOOKUP(B15,'Action List'!$B$4:$E$12,MATCH(C15,{"xxx","Yes","No","n/A"},0),FALSE))=ISNUMBER(MATCH(D15,'Action List'!RedList,0))

What im also finding is that the conditional formatting wants to correct itself for the isnumber and changes the Action List to the document name .... my apologies for being so slow with this !!!!!