Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
delete all cells with color
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
delete all cells with color
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. -- Best Regards, Luke M "relo rob" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
delete all cells with color
Thanks for the advice and sorry for being a newby but I did see any way I
could do a custom filter for color. "Luke M" wrote: 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. -- Best Regards, Luke M "relo rob" wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
delete all cells with color
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 On Thu, 6 Dec 2007 12:36:01 -0800, relo rob wrote: Thanks for the advice and sorry for being a newby but I did see any way I could do a custom filter for color. "Luke M" wrote: 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. -- Best Regards, Luke M "relo rob" wrote: 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
delete all cells with color
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" wrote: 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 On Thu, 6 Dec 2007 12:36:01 -0800, relo rob wrote: Thanks for the advice and sorry for being a newby but I did see any way I could do a custom filter for color. "Luke M" wrote: 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. -- Best Regards, Luke M "relo rob" wrote: 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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
delete all cells with color
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 On Thu, 6 Dec 2007 14:16:01 -0800, relo rob wrote: 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" wrote: 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 On Thu, 6 Dec 2007 12:36:01 -0800, relo rob wrote: Thanks for the advice and sorry for being a newby but I did see any way I could do a custom filter for color. "Luke M" wrote: 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. -- Best Regards, Luke M "relo rob" wrote: 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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
delete all cells with color
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" wrote: 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" wrote: 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 On Thu, 6 Dec 2007 12:36:01 -0800, relo rob wrote: Thanks for the advice and sorry for being a newby but I did see any way I could do a custom filter for color. "Luke M" wrote: 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. -- Best Regards, Luke M "relo rob" wrote: 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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
delete all cells with color
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" wrote: 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" wrote: 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" wrote: 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 On Thu, 6 Dec 2007 12:36:01 -0800, relo rob wrote: Thanks for the advice and sorry for being a newby but I did see any way I could do a custom filter for color. "Luke M" wrote: 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. -- Best Regards, Luke M "relo rob" wrote: 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? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
delete all cells with color
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). "relo rob" wrote: 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" wrote: 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" wrote: 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" wrote: 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 On Thu, 6 Dec 2007 12:36:01 -0800, relo rob wrote: Thanks for the advice and sorry for being a newby but I did see any way I could do a custom filter for color. "Luke M" wrote: 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. -- Best Regards, Luke M "relo rob" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to color automatically color code sums in cells | Charts and Charting in Excel | |||
How do I apply color in cells ..color now shown only in print prev | Excel Worksheet Functions | |||
Use color for delete column or cell icons | Setting up and Configuration of Excel | |||
Excel 2003 will not display color fonts or color fill cells | Excel Worksheet Functions | |||
My excel 2003 wont let me fill cells with color or color the tabs. | New Users to Excel |