Delete all rows if cell color is yellow

[trap]

Hi all,

I have a sheet using conditional formatting to highlight cells that I want to keep. I'd like to have a macro that will delete all rows without yellow cells. I think autofilter would be the best thing to use, but after searching the forums and trying a number of different approaches, I can't quite get it to work.

Alternatively, if there is a quick way to just delete all rows that don't meet the conditional formatting criteria, that would be great too. My formula for the conditional formatting is: =OR((($AA2>0)*($AA2<>"***")),($AU2>0))

I want to keep any cells that meet that criteria. Either by formatting first and then deleting all non-colored cells, or just deleting every cell that doesn't meet that criteria. Thanks in advance for any help!

 

[Jim885]

Hi,

If you do a little bit of research, you could find the answer yourself. Many of the new questions being asked have already been answered. I did a google search "vba delete rows on color" and found this in less time than it took to type this.

http://www.mrexcel.com/forum/excel-...criteria-using-visual-basic-applications.html

All you would need to do is change the color as the condition for the row(s) you want to delete.

You can even do a search for the codes of the colors.

 

[trap]

Yes, as I mentioned I had already worked on this - actually finding the exact same thing you directed me to on Google. I had tried using that line of code earlier and changing the color to Yellow without success. So I changed my cell colors to red to see if I was just missing the color code. Still wouldn't work.

I agree with you and 90+% of the time I'm working on a VBA problem, I'm able to find answers to what I'm looking to do by searching google or these forums. Often tweaking the formulas slightly, or incorporating a couple different items. I have worked on this and tried 5 or 6 different approaches already before asking the question.

I think this is probably fairly simple, but I can't get it to work like I want. I did find a basic solution that just autofilters without deleting rows. It will work for now unless someone has a quick idea on how to delete out the rows. Here is that code for reference, in case it helps or someone else is looking for a similar solution.

Sub AutoFilterYellow()


Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
LastRow& = Range("B:C").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$2:$BR" & LastRow).AutoFilter Field:=1, Criteria1:=RGB(255, _
255, 0), Operator:=xlFilterCellColor
Range("A2").Select
End Sub

 

[Jim885]

In what column will the cell be yellow?

 

[trap]

Yellow in all columns from "A" through "BO". That being the case though, if a cell in any one of those columns was yellow, the whole row could be deleted.

In what column will the cell be yellow?