delete all cells with color

[relo rob]

I have used conditional formatting with a formula is statement to highlights
cells in a column of about 5800 rows. Now that certain cells are highlighted
with one color how can I delete just them cells so I can work with the data
that is left?

 

[Luke M]

I'd use an Autofilter, then do a custom filter to find those cells. Then just
select them all in one go, and delete them.

 

[relo rob]

Thanks for the advice and sorry for being a newby but I did see any way I
could do a custom filter for color.

 

[Gord Dibben]

I think Luke meant for you to filter for the value you used in CF to color the
cells.

i.e. Less than 123 or equal to "relo rob"


Gord Dibben MS Excel MVP

 

[relo rob]

There are 19 different values. I used the 19 values (reps) on sheet 1 and
used color yellow as the highlight. If there was a way to select all 19 from
the drop down in auto filter I wouldn't have the need to color them yellow. I
am using excel 03' if that makes a difference.

 

[Gord Dibben]

Since your yellow color is produced by CF you can't use the normal VBA
interior.colorindex property to find the color.

See Chip Pearson's site for finding CF colors.


Gord

 

[JMB]

You could use a helper column. If the 19 values you want deleted are listed
on Sheet2 (in A1:A19), then enter
=Isnumber(Match(Cell, Sheet2!A$1:A$19, 0))
in an empty column next to your table and copy the formula down (replace
Cell with your actual cell reference). Then autofilter on this new column
for TRUE values and delete the filtered rows. Then turn off autofilter and
delete the helper column.

 

[relo rob]

Thanks, that worked awesome and it was relayed in terms a newbie like me
could understand.

To take it one step further say I have narrowed my original list of 6,500
rows down to 1,000 and in these rows there are columns A thru V. In column M
are names (last, first) I am using the following =COUNTIF($M$9:$M9,M9)>1 to
highlight duplicates another color leaving the first instance with no fill. I
have also copied and pasted the new info (the 1,000) into a new file so now I
am back to one sheet titled YTD Fundings. Is there a way to group the
duplicates that are highligthed with the above formula? I no longer have a
list to reference on Sheet 1? Thanks.

 

[JMB]

perhaps you could enter the formula in an empty cell next to your data (copy
it down) and sort the entire table on this new column (select the table and
click Data/Sort). or use autofilter on this new column, select "custom" from
the drop down arrow and specify values greater than 1. then delete the
results and turn off autofilter.

basically, any formula you enter in CF could be entered into a helper column
(to be used by XL's sort or autofilter features). that is the point the
other responders are also trying to make. CF can identify the data you want
isolated, but it won't help you with the actual process of isolating the data
so that you can easily delete/manipulate it (not without some VBA assistance
- which is more work than is needed).