Can I use color of cell in an "If" function?

  • Thread starter Thread starter George M
  • Start date Start date
G

George M

Here is what I am attempting to do:

If I have a column of numbers (rows 1 through 50)
rows 3,7,12 are colored yellow.
I want to add up the numbers in only columns 3, 7, and 12 (the cells that
are colored yellow)

Can this be done with a formula?

Thanks
George M
 
George

If the cells are colored by Conditional Formatting, use that criteria(on) to
SUM the cells.

If not, see Chip Pearson's site for Functions that operate on colored cells.

Gord Dibben Excel MVP
 
Gord

Thanks for the feedback. I am using fill colors - not conditional. I went to
Chip Pearson's site and studied the series on working with colors. I copied
his scripts and formula, but get nothing but #name? errors.
Don't know what is wrong unless I am using the wrong value for Yellow. I am
trying to search to see if the value is different that what I am using. I am
using 6 because I did read somewhere that Yellow was 6.

I also tried to use Chip's script to get the color of a cell, but get the
same error on that.

By the way, I am using Excel 2003, if that makes a difference.

George M



Gord Dibben said:
George

If the cells are colored by Conditional Formatting, use that criteria(on) to
SUM the cells.

If not, see Chip Pearson's site for Functions that operate on colored cells.

Gord Dibben Excel MVP
Click to expand...
 
George

The #NAME error usually means that Excel does not recognise the Function.

I assume you copied the User Defined Functions from Chip's site.

You would paste them into a General Module in your workbook and Excel would
recognise them.

In fact, they would show up in the Function Wizard category "User Defined".

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste Chip's code in there. Save the workbook
and hit ALT + Q to return to your workbook.

Enter the appropriate formula......probably =sumbycolor(range,6)

To get the index number of a cell........

Enter =cellcolorindex(cellref)

Gord

Gord

Thanks for the feedback. I am using fill colors - not conditional. I went to
Chip Pearson's site and studied the series on working with colors. I copied
his scripts and formula, but get nothing but #name? errors.
Don't know what is wrong unless I am using the wrong value for Yellow. I am
trying to search to see if the value is different that what I am using. I am
using 6 because I did read somewhere that Yellow was 6.

I also tried to use Chip's script to get the color of a cell, but get the
same error on that.

By the way, I am using Excel 2003, if that makes a difference.

George M
Click to expand...
 
George,

It's more straightforward to put something in a column that identifies the
cells to be colored, then make cells colored as a function of that data
using either conditional formatting or a macro if you need more than three
colors. Then do your summing based on that column, not the color directly.
For more on this, see www.smokeylake.com/excel. Go to "Excel truths," and
read "Color as data."
 
Thanks again Gord,

That cleared it up. Works great.
What a great resource Chip's site is!

George M


Gord Dibben said:
George

The #NAME error usually means that Excel does not recognise the Function.

I assume you copied the User Defined Functions from Chip's site.

You would paste them into a General Module in your workbook and Excel would
recognise them.

In fact, they would show up in the Function Wizard category "User Defined".

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste Chip's code in there. Save the workbook
and hit ALT + Q to return to your workbook.

Enter the appropriate formula......probably =sumbycolor(range,6)

To get the index number of a cell........

Enter =cellcolorindex(cellref)

Gord

Click to expand...
 
Back
Top