conditional formatting
 

How can I format a cell based on the color fill of another cell?

Also, how could I add up all the cells that contain a certain color fill?

In your first cell type in e.g.
r for red
g for green

Then do a conditional format on cell 1 to say if cell = r then format it
font red & pattern red.... then add 2nd condition if cell = g then
format it font green & pattern green.. this will change it a particular
colour.

Then in your 2nd cell type in = the 1st cell. this will then have either
a letter r or g appearing. Then just do exactly the same conditional format
conditions as you did in 1st cell.

To add up all the r's do a sumif( ) = r



"Phillip Bruce" wrote:

How can I format a cell based on the color fill of another cell?

Also, how could I add up all the cells that contain a certain color fill?

Thank you for the advice, but for my example these steps do not work. First,
cell 1 must = a value, not "r" or "g" in order to keep the integrity of the
worksheet.

Secondly, the 2nd cell must also be a number (or formula) in order to sum
the cells. I cannot change this cell to "r" or "g" either. The conditional
formatting must be based solely on the fill colour of the 1st cell, not on a
value or formula.

"Phil Wales" wrote:

In your first cell type in e.g.
r for red
g for green

Then do a conditional format on cell 1 to say if cell = r then format it
font red & pattern red.... then add 2nd condition if cell = g then
format it font green & pattern green.. this will change it a particular
colour.

Then in your 2nd cell type in = the 1st cell. this will then have either
a letter r or g appearing. Then just do exactly the same conditional format
conditions as you did in 1st cell.

To add up all the r's do a sumif( ) = r



"Phillip Bruce" wrote:

How can I format a cell based on the color fill of another cell?

Also, how could I add up all the cells that contain a certain color fill?

How is the fill for the first set of cells changed?

Did you use format|conditional formatting for those, too?

If no (you just formatted the cell), you can use a userdefined function to
return the value for the color of the fill.

See Chip Pearson's site for a nice version:
http://www.cpearson.com/excel/colors.htm
Look for: Returning The ColorIndex Of A Cell

Then you could use that UDF in your formula for the conditional formatting for
the other cells.

==========
On the other hand, if you used Format|conditional formatting to color the fill
of the first set of cells, then the code is not for the faint of heart.

Visit Chip Pearson's site for more info:
http://www.cpearson.com/excel/CFColors.htm

====

If possible, I'd try to use a helper cell that mimicked the conditional
formatting formula, then use that cell in the next set of format|conditional
formatting.




Phillip Bruce wrote:

Thank you for the advice, but for my example these steps do not work. First,
cell 1 must = a value, not "r" or "g" in order to keep the integrity of the
worksheet.

Secondly, the 2nd cell must also be a number (or formula) in order to sum
the cells. I cannot change this cell to "r" or "g" either. The conditional
formatting must be based solely on the fill colour of the 1st cell, not on a
value or formula.

"Phil Wales" wrote:

In your first cell type in e.g.
r for red
g for green

Then do a conditional format on cell 1 to say if cell = r then format it
font red & pattern red.... then add 2nd condition if cell = g then
format it font green & pattern green.. this will change it a particular
colour.

Then in your 2nd cell type in = the 1st cell. this will then have either
a letter r or g appearing. Then just do exactly the same conditional format
conditions as you did in 1st cell.

To add up all the r's do a sumif( ) = r



"Phillip Bruce" wrote:

How can I format a cell based on the color fill of another cell?

Also, how could I add up all the cells that contain a certain color fill?

--

Dave Peterson

Is there any other way to accomplish without using a macro or VBA or UDF?

I find your instructions on your link very confusing and complicated.
Frankly, I'd rather do everything by hand if I have to go through that much
trouble to create a macro etc.

"Dave Peterson" wrote:

How is the fill for the first set of cells changed?

Did you use format|conditional formatting for those, too?

If no (you just formatted the cell), you can use a userdefined function to
return the value for the color of the fill.

See Chip Pearson's site for a nice version:
http://www.cpearson.com/excel/colors.htm
Look for: Returning The ColorIndex Of A Cell

Then you could use that UDF in your formula for the conditional formatting for
the other cells.

==========
On the other hand, if you used Format|conditional formatting to color the fill
of the first set of cells, then the code is not for the faint of heart.

Visit Chip Pearson's site for more info:
http://www.cpearson.com/excel/CFColors.htm

====

If possible, I'd try to use a helper cell that mimicked the conditional
formatting formula, then use that cell in the next set of format|conditional
formatting.




Phillip Bruce wrote:

Thank you for the advice, but for my example these steps do not work. First,
cell 1 must = a value, not "r" or "g" in order to keep the integrity of the
worksheet.

Secondly, the 2nd cell must also be a number (or formula) in order to sum
the cells. I cannot change this cell to "r" or "g" either. The conditional
formatting must be based solely on the fill colour of the 1st cell, not on a
value or formula.

"Phil Wales" wrote:

In your first cell type in e.g.
r for red
g for green

Then do a conditional format on cell 1 to say if cell = r then format it
font red & pattern red.... then add 2nd condition if cell = g then
format it font green & pattern green.. this will change it a particular
colour.

Then in your 2nd cell type in = the 1st cell. this will then have either
a letter r or g appearing. Then just do exactly the same conditional format
conditions as you did in 1st cell.

To add up all the r's do a sumif( ) = r



"Phillip Bruce" wrote:

How can I format a cell based on the color fill of another cell?

Also, how could I add up all the cells that contain a certain color fill?

--

Dave Peterson

No matter which way the cells are formatted (manually or via conditional
formatting), you need some kind of VBA to get the color.

Phillip Bruce wrote:

Is there any other way to accomplish without using a macro or VBA or UDF?

I find your instructions on your link very confusing and complicated.
Frankly, I'd rather do everything by hand if I have to go through that much
trouble to create a macro etc.

"Dave Peterson" wrote:

How is the fill for the first set of cells changed?

Did you use format|conditional formatting for those, too?

If no (you just formatted the cell), you can use a userdefined function to
return the value for the color of the fill.

See Chip Pearson's site for a nice version:
http://www.cpearson.com/excel/colors.htm
Look for: Returning The ColorIndex Of A Cell

Then you could use that UDF in your formula for the conditional formatting for
the other cells.

==========
On the other hand, if you used Format|conditional formatting to color the fill
of the first set of cells, then the code is not for the faint of heart.

Visit Chip Pearson's site for more info:
http://www.cpearson.com/excel/CFColors.htm

====

If possible, I'd try to use a helper cell that mimicked the conditional
formatting formula, then use that cell in the next set of format|conditional
formatting.




Phillip Bruce wrote:

Thank you for the advice, but for my example these steps do not work. First,
cell 1 must = a value, not "r" or "g" in order to keep the integrity of the
worksheet.

Secondly, the 2nd cell must also be a number (or formula) in order to sum
the cells. I cannot change this cell to "r" or "g" either. The conditional
formatting must be based solely on the fill colour of the 1st cell, not on a
value or formula.

"Phil Wales" wrote:

In your first cell type in e.g.
r for red
g for green

Then do a conditional format on cell 1 to say if cell = r then format it
font red & pattern red.... then add 2nd condition if cell = g then
format it font green & pattern green.. this will change it a particular
colour.

Then in your 2nd cell type in = the 1st cell. this will then have either
a letter r or g appearing. Then just do exactly the same conditional format
conditions as you did in 1st cell.

To add up all the r's do a sumif( ) = r



"Phillip Bruce" wrote:

How can I format a cell based on the color fill of another cell?

Also, how could I add up all the cells that contain a certain color fill?

--

Dave Peterson


--

Dave Peterson