Check cells for color index and delete

I am trying to write some code and I am lost.

Column Y-has Y, N entered in cells
Column R-has 1,2, etc... entered in cells (some cells have color index 26)
Column O-has 1, 2, etc... entered in cells (some cells have color index 3)
Column G-has names entered entered in cells (some cells have color index 38)

I need the macro to check if Column Y has a "N" and then if it does check Column R, O, G and if there is no color index in any of the Columns the macro will delete the row.

If Column Y has a "N" but Column R, O, G has a color index it will not delete the row.

There are 3000 rows on the spreadsheet and the macro would need to go through all the rows.

How is the colorindex set? If it is by a conditional format, it makes a difference.

You will need to install the routines on Chip's site.

Please Login or Register  to view this content.

I went to Chip's site and looked for routines to install. I could not find anything. Am I missing something?

I would be glad to add this link to the cross post. How do I add it?

Below are the links to the cross posts.

http://www.techsupportforum.com/micr...ml#post1611860

http://www.oooforum.org/forum/viewto...=292912#292912

http://www.xtremevbtalk.com/showthre...86#post1298786

http://excelusergroup.org/forums/p/805/2494.aspx#2494

http://www.andreavb.com/forum/viewto...ID=8275&page=0

http://forums.devx.com/showthread.ph...995#post507995

I have posted this question many places because I have been working on this code for several weeks and have had no luck in getting the code to do what I need. Everyone points me to Chip's site but I am not sure what I am missing. I do appreciate everyone that has taken the time to help with this.

Kenneth-

I have gone to Chip's website. I have added the Function ActiveCondition, Function ColorIndexOfCF, Function ColorOfCF, Function GetStrippedValue, Function CountOfCF, and the Function SumByCFColorIndex. I added these functions above the code that you provided. Do I need all of these functions? Do I need to edit these functions to work with my data?

Here is an example xls with Chip's routines and the one that I posted.

ColorIndexOfCF requires the ActiveCondition. I just put them all in a separate module.

Ok I have updated that in the routine.

I am looking into Chip's routine more. From these tests, one can see that the conditional formula1 is returned as =AC2 rather than =Q2. Chip talked about the using absolute reference for the Formula Is routines but not for the Cell Value Is.

Please Login or Register  to view this content.

I am glad to see that I am not the only one having issues with this. I was thinking that I was missing something because no matter what I was trying nothing was working.

Yes, that would be easier and more reliable. It is easy to add a worksheet event. It can easily set the interior color for you based on a change event. Once the change events set the colors as needed, the delete routine would be similar to what I was using.

I will look at this tonight if you like.

I added routines to strip conditional formatting, add interior colorindexes, worksheet change event and the code to delete the rows.

http://www.mediafire.com/?ksyj03mxy95

Kenneth,

It works great. I can not thank you enough this will save me about 2 hours each day. I can spend the time doing something more productive. You Rock!

If I wanted to add another Column (Column W) to check if there is a color in it. I know that I would need to update the routine with the extra column. I have added it and it still does not recognize it. I have added the code that I updated below and bolded what I added. What am I missing?

Please Login or Register  to view this content.

That should work. Make sure that they have no conditional formats that override the colorindex.

That conditional format will get you every time. Thanks