Using Conditional Formatting to delete rows

[msk7777]

I have a worksheet that I use Conditional Formatting to check the dates in a total of 5 columns. In those columns, if the date is before 8/1/2006, it changes the cell color to red. Is there a formula I can put into Condition 2 to that would then clear out the whole row if within those 5 columns there isn't a red cell? This would take hours of work off of my slab. Thanks!

Credetnialing Master.xls
	AT	AU	AV	AW	AX	AY	AZ	BA
1	LicenseExpiration	InsuranceExpiration	DEA_Expiration	State	StateDrugNumber	StateDrugExpiration	AttestationDate	NPDB_Date
2	12/31/2007	8/2/2007	1/31/2009		2293	12/31/2007	3/7/2007	3/26/2007
3	12/31/2007	5/1/2008	2/28/2010		2538	12/31/2007	7/6/2006	7/24/2006
4	12/31/2007	8/26/2007	12/31/2007		2563	12/31/2007	12/12/2006	12/14/2006
5	12/31/2007	10/19/2007	12/31/2009		2768	12/31/2007	11/28/2004	12/8/2004
6	12/31/2007	9/25/2007	8/31/2007		3030	12/31/2007	4/6/2006	4/17/2006
7	12/31/2007	12/3/2007	7/31/2009		3152	12/31/2007	5/3/2005	5/11/2005
8	12/31/2007	8/2/2007	8/31/2008		3236	12/31/2007	2/28/2007	3/26/2007
9			10/31/2009					7/7/2003
10	12/31/2007	8/10/2007	3/31/2008		3535	12/31/2007	4/11/2006	4/24/2006
11	12/31/2007	2/1/2008	3/31/2009		3650	12/31/2007	4/4/2006	4/24/2006
12	12/31/2007	7/1/2008	8/31/2009		3923	12/31/2007	3/8/2007	3/26/2007
13	12/31/2007	7/1/2008	9/30/2009		3935	12/31/2007	12/11/2006	12/21/2006
14	12/31/2007	4/25/2008	2/28/2010		4030	12/31/2007	5/30/2006	6/5/2006
15	12/31/2007	8/1/2007	5/31/2010		4133	12/31/2007	11/29/2005	2/7/2006
16	12/31/2007	6/24/2007	1/31/2008		4391	12/31/2007	4/18/2006	4/24/2006
17	12/31/2006	11/1/2005	7/31/2009		4077	12/31/2006	3/12/2004	10/22/2002
18	12/31/2007	3/30/2008	9/30/2007		4509	12/31/2007	11/17/2005	11/21/2005
19			10/31/2008				11/11/2001	11/7/2001
20	12/31/2007	7/16/2008	9/30/2008		4572	12/31/2007	4/21/2005	4/27/2005
21	12/31/2007	7/22/2008			NONE		4/7/2006	4/17/2006
22	12/31/2007	7/1/2008	10/31/2008		4704	12/31/2007	5/19/2006	5/24/2006
23	12/31/2007	5/17/2009	2/29/2008		4737	12/31/2007	10/21/2004	10/4/2004
24	12/31/2007	10/1/2007	3/31/2010		4952	12/31/2007	5/3/2006	5/15/2006
25	12/31/2007	2/1/2008	9/30/2008		5006	12/31/2007	5/31/2006	6/26/2006
26	12/31/2007	10/1/2007	8/31/2008		5033	12/31/2007	6/13/2005	6/15/2005
27			10/31/2009					10/20/2003
28	12/31/2007	10/28/2007	2/29/2008				4/12/2006	4/17/2006
29	12/31/2007	11/1/2007	3/31/2009				8/18/2005	9/19/2005
Sheet1

 

[msk7777]

Ok well here, maybe this will help...this spreadsheet is used to look at every person in our system that may have 1 of the 5 different licenses and see which ones are expired (column AT, AU, AW and AZ). This spreadsheet has over 23,000 rows. I need to somehow identify the ones that have an expiration date that is less than (or equal to)8/31/2007 (which will also pick up the blank cells because that means we don't have a updated license). Basically I only need to keep the rows that have a lesser date than (or equal to) 8/31/2007 and get rid of the rest of the rows so we can run a fax merge and fax only the ones that the licenses are expired. I'm not familiar with VBA, the only thing I do close to it is recording Macros. If anyone can maybe walk me through it I know I can pick it up from there.

 

[Norie]

How exactly are you doing the merge?

If you are using Word mail merge you could specify criteria. ie a date

 

[msk7777]

It's not through Word it is through our Fax Serv software, but let me make sure first.

 

[iggydarsa]

this spreadsheet is used to look at every person in our system that may have 1 of the 5 different licenses and see which ones are expired (column AT, AU, AW and AZ)

are you sure these are the correct columns? you said there qould be 5 column but you only told 4 of them. Also, column AW is a column for state not for date...

 

[msk7777]

I'm terribly sorry, yes it should be AY not AW, and after posting that message I had talked to the person I am helping with this and they only need the 4 columns and not 5.