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

 

[Norie]

You can't use conditional formatting to delete.

It's, well, for formatting.

 

[iggydarsa]

however you can use a VBA for that

 

[msk7777]

I wasn't more or less wanting the whole row deleted, it would be fine if it would just clear the contents within the cell

 

[msk7777]

however you can use a VBA for that

Iggy how would I go about doing that?

 

[iggydarsa]

deleting is not "formatting" so i dont know if conditional formatting can do it but you can always make font color white so it doesnt show up in front of a white background ;]

 

[Norie]

You can't clear the contents of a cell using conditional formatting either.

It's for formatting.

Could you not use filters?

 

[iggydarsa]

Iggy how would I go about doing that?

well, if you tell me what conditions and in which columns your deleting criteria is we can come up with a macro and show you how to run it.

 

[msk7777]

Iggy: that wouldn't work because I need to sort the sheet so I can delete the rows. If the row still had information in it but just in a white font in it, the sort function would still read the data that was in the cell correct?

 

[iggydarsa]

If the row still had information in it but just in a white font in it, the sort function would still read the data that was in the cell correct?

correct... for that you'll need either filter or if you want to get rid of the data for good you'll need a VBA