Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 8th 07, 08:53 PM posted to microsoft.public.excel.worksheet.functions
VTN VTN is offline
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 1
Default 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   Report Post  
Old February 8th 07, 08:58 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 2,574
Default 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   Report Post  
Old February 8th 07, 09:55 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,180
Default 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   Report Post  
Old February 8th 07, 10:06 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,886
Default 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   Report Post  
Old February 8th 07, 10:14 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,908
Default 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   Report Post  
Old February 8th 07, 10:56 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,908
Default 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   Report Post  
Old February 8th 07, 10:56 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,908
Default 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   Report Post  
Old February 9th 07, 07:41 AM posted to microsoft.public.excel.worksheet.functions
prc prc is offline
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
where to put results of find operation in find and replace functio DEP Excel Worksheet Functions 5 November 15th 06 07:52 PM
Find Highlighted Cells Gayla Excel Discussion (Misc queries) 1 November 2nd 06 07:23 PM
adding several worksheet cells onto a main worksheet oxicottin Excel Worksheet Functions 2 September 20th 06 08:07 PM
Linking cells in a worksheet to other worksheets in a workbook Dave Excel Discussion (Misc queries) 4 June 24th 05 06:18 PM
Heps to design Locked/Unlocked cells in protected worksheet Kevin Excel Discussion (Misc queries) 0 December 30th 04 07:09 AM


All times are GMT +1. The time now is 07:31 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017