Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Based on Colors in a Horz Range of Cells
I have a co-worker who wants to insert a formula to say "If all of the cells
in a range are green, then this cell is green. If some cells are yellow, then this cell is yellow. If any cells are red, this cell is red". ?? DOUG |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Based on Colors in a Horz Range of Cells
Sadly, you can't write a formula based on colors. (Well, without using VBA)
However, is there some function/conditional format making the other cells these colors? If so, you/we can create a formula based on that. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DOUG ECKERT" wrote: I have a co-worker who wants to insert a formula to say "If all of the cells in a range are green, then this cell is green. If some cells are yellow, then this cell is yellow. If any cells are red, this cell is red". ?? DOUG |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Based on Colors in a Horz Range of Cell
Luke: A VBA-based answer would be nice. However...
There is a cell (N2) containing the goal value of "80" (percent). If I knew how to include all of the cells in the range in the "IF" statement, I could say "If all of these cells are greater than or equal to cell N2, then color this cell green - (or, failing that, make this cell say "YES" and then conditionally format all of the "YES" cells as green). They prefer a green stoplight to green fill, by the way. There is a second cell for the "Yellow" cutoff, too. DOUG "Luke M" wrote: Sadly, you can't write a formula based on colors. (Well, without using VBA) However, is there some function/conditional format making the other cells these colors? If so, you/we can create a formula based on that. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DOUG ECKERT" wrote: I have a co-worker who wants to insert a formula to say "If all of the cells in a range are green, then this cell is green. If some cells are yellow, then this cell is yellow. If any cells are red, this cell is red". ?? DOUG |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Based on Colors in a Horz Range of Cells
'Anybody?
DOUG "DOUG ECKERT" wrote: I have a co-worker who wants to insert a formula to say "If all of the cells in a range are green, then this cell is green. If some cells are yellow, then this cell is yellow. If any cells are red, this cell is red". ?? DOUG |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Based on Colors in a Horz Range of Cells
How did the cells get to be green or yellow?
If by Conditional Formatting, use the CF criteria in your formula. If manually colored you will need to add a User Defined Function to ascertain the color. See Chip Pearson's site for some code. http://www.cpearson.com/excel/colors.aspx Gord Dibben MS Excel MVP On Wed, 17 Dec 2008 11:24:09 -0800, DOUG ECKERT wrote: 'Anybody? DOUG "DOUG ECKERT" wrote: I have a co-worker who wants to insert a formula to say "If all of the cells in a range are green, then this cell is green. If some cells are yellow, then this cell is yellow. If any cells are red, this cell is red". ?? DOUG |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Based on Colors in a Horz Range of Cell
Gord: I do not know how to put CF in a formula, beyond using the CF tool.
What this person wants to do is to look at all of the compliant cells in a row. If ALL cells are compliant, the overall grade is a GREEN stoplight - as in, "Good to Go". If ANY cells are rated marginal - with scores in the marginal or "YELLOW" range - the summary field would show a YELLOW stoplight. If rated below the YELLOW range, it would show a RED stoplight. Any RED cells would generate a RED stoplight in the summary field. (Compliance will be noted as above a certain threshold equals YES, or in some cases YES equals Yes, based on checklist questions. The individual cells contain numbers to be compared to a certain goal, which is reflected in another cell, at least in most cases). In short, either ALL Comply, SOME Comply (with no RED scores), or NONE comply (if ANY RED scores). So far, I have tried the IF, AND and LOOKUP functions, to great frustration. Cell colors are based on Conditional Formatting, but the formula could reference the cell containing the compliance goal, at least for generating the GREEN stoplight. Adding the YELLOW and RED criteria is a great deal more complicated. "IF" worked, until I ran out of nested functions to use. "Gord Dibben" wrote: How did the cells get to be green or yellow? If by Conditional Formatting, use the CF criteria in your formula. If manually colored you will need to add a User Defined Function to ascertain the color. See Chip Pearson's site for some code. http://www.cpearson.com/excel/colors.aspx Gord Dibben MS Excel MVP On Wed, 17 Dec 2008 11:24:09 -0800, DOUG ECKERT wrote: 'Anybody? DOUG "DOUG ECKERT" wrote: I have a co-worker who wants to insert a formula to say "If all of the cells in a range are green, then this cell is green. If some cells are yellow, then this cell is yellow. If any cells are red, this cell is red". ?? DOUG |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Based on Colors in a Horz Range of Cell
I don't know where to start with this.
But what I'm going to do for a while is deal with 25cm of snow that dumped in the past 4 hours...............and more coming all night. I vill be baawwwk Gord On Wed, 17 Dec 2008 12:58:01 -0800, DOUG ECKERT wrote: Gord: I do not know how to put CF in a formula, beyond using the CF tool. What this person wants to do is to look at all of the compliant cells in a row. If ALL cells are compliant, the overall grade is a GREEN stoplight - as in, "Good to Go". If ANY cells are rated marginal - with scores in the marginal or "YELLOW" range - the summary field would show a YELLOW stoplight. If rated below the YELLOW range, it would show a RED stoplight. Any RED cells would generate a RED stoplight in the summary field. (Compliance will be noted as above a certain threshold equals YES, or in some cases YES equals Yes, based on checklist questions. The individual cells contain numbers to be compared to a certain goal, which is reflected in another cell, at least in most cases). In short, either ALL Comply, SOME Comply (with no RED scores), or NONE comply (if ANY RED scores). So far, I have tried the IF, AND and LOOKUP functions, to great frustration. Cell colors are based on Conditional Formatting, but the formula could reference the cell containing the compliance goal, at least for generating the GREEN stoplight. Adding the YELLOW and RED criteria is a great deal more complicated. "IF" worked, until I ran out of nested functions to use. "Gord Dibben" wrote: How did the cells get to be green or yellow? If by Conditional Formatting, use the CF criteria in your formula. If manually colored you will need to add a User Defined Function to ascertain the color. See Chip Pearson's site for some code. http://www.cpearson.com/excel/colors.aspx Gord Dibben MS Excel MVP On Wed, 17 Dec 2008 11:24:09 -0800, DOUG ECKERT wrote: 'Anybody? DOUG "DOUG ECKERT" wrote: I have a co-worker who wants to insert a formula to say "If all of the cells in a range are green, then this cell is green. If some cells are yellow, then this cell is yellow. If any cells are red, this cell is red". ?? DOUG |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Based on Colors in a Horz Range of Cell
I'm not Gord (and he's thankful everyday for that!), but how about sharing some
more info. What is the cell that contains the threshold value? What are the columns in each row that contain the value to be compared? When you're doing the conditional formatting, how do you know if the cell should be yellow or red? I would assume that if the value in the cell is greater than or equal to the threshold, then it's marked green. But how do you determine Red from Yellow? DOUG ECKERT wrote: Gord: I do not know how to put CF in a formula, beyond using the CF tool. What this person wants to do is to look at all of the compliant cells in a row. If ALL cells are compliant, the overall grade is a GREEN stoplight - as in, "Good to Go". If ANY cells are rated marginal - with scores in the marginal or "YELLOW" range - the summary field would show a YELLOW stoplight. If rated below the YELLOW range, it would show a RED stoplight. Any RED cells would generate a RED stoplight in the summary field. (Compliance will be noted as above a certain threshold equals YES, or in some cases YES equals Yes, based on checklist questions. The individual cells contain numbers to be compared to a certain goal, which is reflected in another cell, at least in most cases). In short, either ALL Comply, SOME Comply (with no RED scores), or NONE comply (if ANY RED scores). So far, I have tried the IF, AND and LOOKUP functions, to great frustration. Cell colors are based on Conditional Formatting, but the formula could reference the cell containing the compliance goal, at least for generating the GREEN stoplight. Adding the YELLOW and RED criteria is a great deal more complicated. "IF" worked, until I ran out of nested functions to use. "Gord Dibben" wrote: How did the cells get to be green or yellow? If by Conditional Formatting, use the CF criteria in your formula. If manually colored you will need to add a User Defined Function to ascertain the color. See Chip Pearson's site for some code. http://www.cpearson.com/excel/colors.aspx Gord Dibben MS Excel MVP On Wed, 17 Dec 2008 11:24:09 -0800, DOUG ECKERT wrote: 'Anybody? DOUG "DOUG ECKERT" wrote: I have a co-worker who wants to insert a formula to say "If all of the cells in a range are green, then this cell is green. If some cells are yellow, then this cell is yellow. If any cells are red, this cell is red". ?? DOUG -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Based on Colors in a Horz Range of Cell
Dave: The colors are all determined by whether the cells meet or exceed the
cut points, or goals, which are contained in other cells. It is set up using the standard conditional formatting features. DOUG "Dave Peterson" wrote: I'm not Gord (and he's thankful everyday for that!), but how about sharing some more info. What is the cell that contains the threshold value? What are the columns in each row that contain the value to be compared? When you're doing the conditional formatting, how do you know if the cell should be yellow or red? I would assume that if the value in the cell is greater than or equal to the threshold, then it's marked green. But how do you determine Red from Yellow? DOUG ECKERT wrote: Gord: I do not know how to put CF in a formula, beyond using the CF tool. What this person wants to do is to look at all of the compliant cells in a row. If ALL cells are compliant, the overall grade is a GREEN stoplight - as in, "Good to Go". If ANY cells are rated marginal - with scores in the marginal or "YELLOW" range - the summary field would show a YELLOW stoplight. If rated below the YELLOW range, it would show a RED stoplight. Any RED cells would generate a RED stoplight in the summary field. (Compliance will be noted as above a certain threshold equals YES, or in some cases YES equals Yes, based on checklist questions. The individual cells contain numbers to be compared to a certain goal, which is reflected in another cell, at least in most cases). In short, either ALL Comply, SOME Comply (with no RED scores), or NONE comply (if ANY RED scores). So far, I have tried the IF, AND and LOOKUP functions, to great frustration. Cell colors are based on Conditional Formatting, but the formula could reference the cell containing the compliance goal, at least for generating the GREEN stoplight. Adding the YELLOW and RED criteria is a great deal more complicated. "IF" worked, until I ran out of nested functions to use. "Gord Dibben" wrote: How did the cells get to be green or yellow? If by Conditional Formatting, use the CF criteria in your formula. If manually colored you will need to add a User Defined Function to ascertain the color. See Chip Pearson's site for some code. http://www.cpearson.com/excel/colors.aspx Gord Dibben MS Excel MVP On Wed, 17 Dec 2008 11:24:09 -0800, DOUG ECKERT wrote: 'Anybody? DOUG "DOUG ECKERT" wrote: I have a co-worker who wants to insert a formula to say "If all of the cells in a range are green, then this cell is green. If some cells are yellow, then this cell is yellow. If any cells are red, this cell is red". ?? DOUG -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Based on Colors in a Horz Range of Cell
Can you describe the cells that get shaded and share the rules for each of those
cells? Right now, I don't see how anyone can take a guess. DOUG ECKERT wrote: Dave: The colors are all determined by whether the cells meet or exceed the cut points, or goals, which are contained in other cells. It is set up using the standard conditional formatting features. DOUG "Dave Peterson" wrote: I'm not Gord (and he's thankful everyday for that!), but how about sharing some more info. What is the cell that contains the threshold value? What are the columns in each row that contain the value to be compared? When you're doing the conditional formatting, how do you know if the cell should be yellow or red? I would assume that if the value in the cell is greater than or equal to the threshold, then it's marked green. But how do you determine Red from Yellow? DOUG ECKERT wrote: Gord: I do not know how to put CF in a formula, beyond using the CF tool. What this person wants to do is to look at all of the compliant cells in a row. If ALL cells are compliant, the overall grade is a GREEN stoplight - as in, "Good to Go". If ANY cells are rated marginal - with scores in the marginal or "YELLOW" range - the summary field would show a YELLOW stoplight. If rated below the YELLOW range, it would show a RED stoplight. Any RED cells would generate a RED stoplight in the summary field. (Compliance will be noted as above a certain threshold equals YES, or in some cases YES equals Yes, based on checklist questions. The individual cells contain numbers to be compared to a certain goal, which is reflected in another cell, at least in most cases). In short, either ALL Comply, SOME Comply (with no RED scores), or NONE comply (if ANY RED scores). So far, I have tried the IF, AND and LOOKUP functions, to great frustration. Cell colors are based on Conditional Formatting, but the formula could reference the cell containing the compliance goal, at least for generating the GREEN stoplight. Adding the YELLOW and RED criteria is a great deal more complicated. "IF" worked, until I ran out of nested functions to use. "Gord Dibben" wrote: How did the cells get to be green or yellow? If by Conditional Formatting, use the CF criteria in your formula. If manually colored you will need to add a User Defined Function to ascertain the color. See Chip Pearson's site for some code. http://www.cpearson.com/excel/colors.aspx Gord Dibben MS Excel MVP On Wed, 17 Dec 2008 11:24:09 -0800, DOUG ECKERT wrote: 'Anybody? DOUG "DOUG ECKERT" wrote: I have a co-worker who wants to insert a formula to say "If all of the cells in a range are green, then this cell is green. If some cells are yellow, then this cell is yellow. If any cells are red, this cell is red". ?? DOUG -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting Based on Colors in a Horz Range of Cell
This is actually pretty easy, because it's not actually conditional
formatting based on colors, it's conditional formatting to summarize the results of other cells that have conditional formatting applied to them. Either base your "Stoplight cell" colors on ALL the original conditions that created the individual colors, but you seem to have tried that. You could create a separate (perhaps hidden) "indicator cell" that applies all the necessary tests to the individual values in the row to come up with whether the row is green, yellow or red, then format based on that. How do you set up the "indicator cell"? One possible way is by using IF, AND, and OR statements to build a formula that results in 0 if red, 1 if yellow, and 2 if green. There are a lot more options. With more specifics, perhaps I could build an example. It seems you're saying the function is too complex for a single cell, so test the row to see if it meets the yellow criteria in one cell, then if it meets the green in the next. I would probably go back pair the original value cells with hidden or off-screen individual indicator cells that evaluate to "True" or "False" to get the original color formatting for each, then build my "row stoplight" directly from the values in those same cells. I can't imagine how or why a LOOKUP would be useful here, and wouldn't use it. "DOUG ECKERT" wrote: Gord: I do not know how to put CF in a formula, beyond using the CF tool. What this person wants to do is to look at all of the compliant cells in a row. If ALL cells are compliant, the overall grade is a GREEN stoplight - as in, "Good to Go". If ANY cells are rated marginal - with scores in the marginal or "YELLOW" range - the summary field would show a YELLOW stoplight. If rated below the YELLOW range, it would show a RED stoplight. Any RED cells would generate a RED stoplight in the summary field. (Compliance will be noted as above a certain threshold equals YES, or in some cases YES equals Yes, based on checklist questions. The individual cells contain numbers to be compared to a certain goal, which is reflected in another cell, at least in most cases). In short, either ALL Comply, SOME Comply (with no RED scores), or NONE comply (if ANY RED scores). So far, I have tried the IF, AND and LOOKUP functions, to great frustration. Cell colors are based on Conditional Formatting, but the formula could reference the cell containing the compliance goal, at least for generating the GREEN stoplight. Adding the YELLOW and RED criteria is a great deal more complicated. "IF" worked, until I ran out of nested functions to use. "Gord Dibben" wrote: How did the cells get to be green or yellow? If by Conditional Formatting, use the CF criteria in your formula. If manually colored you will need to add a User Defined Function to ascertain the color. See Chip Pearson's site for some code. http://www.cpearson.com/excel/colors.aspx Gord Dibben MS Excel MVP On Wed, 17 Dec 2008 11:24:09 -0800, DOUG ECKERT wrote: 'Anybody? DOUG "DOUG ECKERT" wrote: I have a co-worker who wants to insert a formula to say "If all of the cells in a range are green, then this cell is green. If some cells are yellow, then this cell is yellow. If any cells are red, this cell is red". ?? DOUG |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting Based of Cells Based on Data Entry in anoth | Excel Discussion (Misc queries) | |||
Conditional Formatting Based on two cells in a row | Excel Worksheet Functions | |||
Conditional formatting based on date range | Excel Discussion (Misc queries) | |||
Conditional Formatting based on other cells | Excel Worksheet Functions | |||
Create drop down box with the formatting of range cells colors ect | Excel Worksheet Functions |