Using Conditional Formatting to delete rows

msk7777

Active Member
Joined
Mar 30, 2004
Messages
280
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
ATAUAVAWAXAYAZBA
1LicenseExpirationInsuranceExpirationDEA_ExpirationStateStateDrugNumberStateDrugExpirationAttestationDateNPDB_Date
212/31/20078/2/20071/31/2009229312/31/20073/7/20073/26/2007
312/31/20075/1/20082/28/2010253812/31/20077/6/20067/24/2006
412/31/20078/26/200712/31/2007256312/31/200712/12/200612/14/2006
512/31/200710/19/200712/31/2009276812/31/200711/28/200412/8/2004
612/31/20079/25/20078/31/2007303012/31/20074/6/20064/17/2006
712/31/200712/3/20077/31/2009315212/31/20075/3/20055/11/2005
812/31/20078/2/20078/31/2008323612/31/20072/28/20073/26/2007
910/31/20097/7/2003
1012/31/20078/10/20073/31/2008353512/31/20074/11/20064/24/2006
1112/31/20072/1/20083/31/2009365012/31/20074/4/20064/24/2006
1212/31/20077/1/20088/31/2009392312/31/20073/8/20073/26/2007
1312/31/20077/1/20089/30/2009393512/31/200712/11/200612/21/2006
1412/31/20074/25/20082/28/2010403012/31/20075/30/20066/5/2006
1512/31/20078/1/20075/31/2010413312/31/200711/29/20052/7/2006
1612/31/20076/24/20071/31/2008439112/31/20074/18/20064/24/2006
1712/31/200611/1/20057/31/2009407712/31/20063/12/200410/22/2002
1812/31/20073/30/20089/30/2007450912/31/200711/17/200511/21/2005
1910/31/200811/11/200111/7/2001
2012/31/20077/16/20089/30/2008457212/31/20074/21/20054/27/2005
2112/31/20077/22/2008NONE4/7/20064/17/2006
2212/31/20077/1/200810/31/2008470412/31/20075/19/20065/24/2006
2312/31/20075/17/20092/29/2008473712/31/200710/21/200410/4/2004
2412/31/200710/1/20073/31/2010495212/31/20075/3/20065/15/2006
2512/31/20072/1/20089/30/2008500612/31/20075/31/20066/26/2006
2612/31/200710/1/20078/31/2008503312/31/20076/13/20056/15/2005
2710/31/200910/20/2003
2812/31/200710/28/20072/29/20084/12/20064/17/2006
2912/31/200711/1/20073/31/20098/18/20059/19/2005
Sheet1
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,278
Office Version
  1. 365
Platform
  1. Windows
You can't use conditional formatting to delete.

It's, well, for formatting.:)
 

msk7777

Active Member
Joined
Mar 30, 2004
Messages
280
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
 

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,278
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

It's for formatting.:)

Could you not use filters?
 

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
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

Active Member
Joined
Mar 30, 2004
Messages
280
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

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,808
Members
416,884
Latest member
leeshjay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top