Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find colored cells in a worksheet
Hi,
I have a worksheet that has data in cells which has also been color coded. What I would like to do is find a way to search for, say, all of the yellow cells in the worksheets and sum the numbers in those cells. And then, of course, do the same for all of the other colors in the worksheet. I thought I could perhaps use a sumif where the criteria is "if the cell is shaded yellow" but I don't know how to set that criteria up. Does anyone have a way to do this? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find colored cells in a worksheet
Excel doesn't have a built in way of doing this, however, you can use a macro
to do it: http://www.cpearson.com/excel/colors.htm Post back if you have questions. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "VTN" wrote: Hi, I have a worksheet that has data in cells which has also been color coded. What I would like to do is find a way to search for, say, all of the yellow cells in the worksheets and sum the numbers in those cells. And then, of course, do the same for all of the other colors in the worksheet. I thought I could perhaps use a sumif where the criteria is "if the cell is shaded yellow" but I don't know how to set that criteria up. Does anyone have a way to do this? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find colored cells in a worksheet
Here is a quick way without VBA or formulas.
Edit Find clear "Find What" Options Format Choose Format From Cell select sample cell Find All scroll to end of list, press CTRL+SHIFT and click on the last entry of the list. Right click Status Bar and check Sum. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find colored cells in a worksheet
Hi Herbert
That's a very neat trick! Thanks for sharing with us. -- Regards Roger Govier "Herbert Seidenberg" wrote in message oups.com... Here is a quick way without VBA or formulas. Edit Find clear "Find What" Options Format Choose Format From Cell select sample cell Find All scroll to end of list, press CTRL+SHIFT and click on the last entry of the list. Right click Status Bar and check Sum. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find colored cells in a worksheet
Cannot be done without VBA
See Chip Pearson's site for code. http://www.cpearson.com/excel/colors.htm Gord Dibben MS Excel MVP On Thu, 8 Feb 2007 12:53:02 -0800, VTN wrote: Hi, I have a worksheet that has data in cells which has also been color coded. What I would like to do is find a way to search for, say, all of the yellow cells in the worksheets and sum the numbers in those cells. And then, of course, do the same for all of the other colors in the worksheet. I thought I could perhaps use a sumif where the criteria is "if the cell is shaded yellow" but I don't know how to set that criteria up. Does anyone have a way to do this? Thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find colored cells in a worksheet
And one which I continually forget about<g
Gord On Thu, 8 Feb 2007 22:06:00 -0000, "Roger Govier" wrote: Hi Herbert That's a very neat trick! Thanks for sharing with us. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find colored cells in a worksheet
Well............I see it can be done without VBA.
Thanks Herbert Gord On Thu, 08 Feb 2007 14:14:19 -0800, Gord Dibben <gorddibbATshawDOTca wrote: Cannot be done without VBA See Chip Pearson's site for code. http://www.cpearson.com/excel/colors.htm Gord Dibben MS Excel MVP On Thu, 8 Feb 2007 12:53:02 -0800, VTN wrote: Hi, I have a worksheet that has data in cells which has also been color coded. What I would like to do is find a way to search for, say, all of the yellow cells in the worksheets and sum the numbers in those cells. And then, of course, do the same for all of the other colors in the worksheet. I thought I could perhaps use a sumif where the criteria is "if the cell is shaded yellow" but I don't know how to set that criteria up. Does anyone have a way to do this? Thanks! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find colored cells in a worksheet
Hi VTN!!!!
this stuff u can do it by using the most regular function in excel procedure for the same: 1) ctrl+F 2) click on "option" OR "Alt+t" 3) click on "Format" 4) select "choose from the cell"(make sure nothing is there in column "find what" 5)click on"find all" 6) press "ctrl + 1 7) close the "find box" 8) copy the selected records & paste it into the required excel sheet. 6)various links will be displayed below the find box, click on any of the link 7) "VTN" wrote: Hi, I have a worksheet that has data in cells which has also been color coded. What I would like to do is find a way to search for, say, all of the yellow cells in the worksheets and sum the numbers in those cells. And then, of course, do the same for all of the other colors in the worksheet. I thought I could perhaps use a sumif where the criteria is "if the cell is shaded yellow" but I don't know how to set that criteria up. Does anyone have a way to do this? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
where to put results of find operation in find and replace functio | Excel Worksheet Functions | |||
Find Highlighted Cells | Excel Discussion (Misc queries) | |||
adding several worksheet cells onto a main worksheet | Excel Worksheet Functions | |||
Linking cells in a worksheet to other worksheets in a workbook | Excel Discussion (Misc queries) | |||
Heps to design Locked/Unlocked cells in protected worksheet | Excel Discussion (Misc queries) |