Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formulas
I have three cells that i would like to be green if they all equal each other
and red if they do not. Here is an example: C43 the value is $10.00 C45 the value is $10.00 C49 the value is $10.00 If all three equal each other then I want all three cells to turn green. If one or more of the values do not match then I want all three to turn red |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formulas
Select all three cells (Hold Ctrl to select inidvidual cells)
goto: Format-Conditional Format. Change first box to "Formula is" Input: =AND($C$43=$C$45,$C$43=$C$49) Click format, go to pattern tab, and select green. Hit 'ok' to close dialogue. Now, with all 3 cells still selected, format the cells red (as you would normally). Now, your cells will be red by default, unless they meet your criteria (as you desired). -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Scooter" wrote: I have three cells that i would like to be green if they all equal each other and red if they do not. Here is an example: C43 the value is $10.00 C45 the value is $10.00 C49 the value is $10.00 If all three equal each other then I want all three cells to turn green. If one or more of the values do not match then I want all three to turn red |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formulas
Scooter,
Select the three cells C43, C45, and C49, format them for a red background, then use Format / Conditional Formatting Formula is... option, with the formula =AND($C$43=$C$45,$C$45=$C$49) and set the fill to green for that condition. HTH, Bernie MS Excel MVP "Scooter" wrote in message ... I have three cells that i would like to be green if they all equal each other and red if they do not. Here is an example: C43 the value is $10.00 C45 the value is $10.00 C49 the value is $10.00 If all three equal each other then I want all three cells to turn green. If one or more of the values do not match then I want all three to turn red |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formulas
I am using Excel 2007 and when I click on conditional formatting I do not see
"Formula is" "Luke M" wrote: Select all three cells (Hold Ctrl to select inidvidual cells) goto: Format-Conditional Format. Change first box to "Formula is" Input: =AND($C$43=$C$45,$C$43=$C$49) Click format, go to pattern tab, and select green. Hit 'ok' to close dialogue. Now, with all 3 cells still selected, format the cells red (as you would normally). Now, your cells will be red by default, unless they meet your criteria (as you desired). -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Scooter" wrote: I have three cells that i would like to be green if they all equal each other and red if they do not. Here is an example: C43 the value is $10.00 C45 the value is $10.00 C49 the value is $10.00 If all three equal each other then I want all three cells to turn green. If one or more of the values do not match then I want all three to turn red |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formulas
Hi,
Go to conditional formating, new rule, the last option from the Select a rule type is the "use a formula to determine which cells to format" "Scooter" wrote: I am using Excel 2007 and when I click on conditional formatting I do not see "Formula is" "Luke M" wrote: Select all three cells (Hold Ctrl to select inidvidual cells) goto: Format-Conditional Format. Change first box to "Formula is" Input: =AND($C$43=$C$45,$C$43=$C$49) Click format, go to pattern tab, and select green. Hit 'ok' to close dialogue. Now, with all 3 cells still selected, format the cells red (as you would normally). Now, your cells will be red by default, unless they meet your criteria (as you desired). -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Scooter" wrote: I have three cells that i would like to be green if they all equal each other and red if they do not. Here is an example: C43 the value is $10.00 C45 the value is $10.00 C49 the value is $10.00 If all three equal each other then I want all three cells to turn green. If one or more of the values do not match then I want all three to turn red |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formulas
Scooter,
In 2007, use the CF button, then choose "Highlight Cell Rules" "More Rules" "Use a formula"..... HTH, Bernie MS Excel MVP "Scooter" wrote in message ... I am using Excel 2007 and when I click on conditional formatting I do not see "Formula is" "Luke M" wrote: Select all three cells (Hold Ctrl to select inidvidual cells) goto: Format-Conditional Format. Change first box to "Formula is" Input: =AND($C$43=$C$45,$C$43=$C$49) Click format, go to pattern tab, and select green. Hit 'ok' to close dialogue. Now, with all 3 cells still selected, format the cells red (as you would normally). Now, your cells will be red by default, unless they meet your criteria (as you desired). -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Scooter" wrote: I have three cells that i would like to be green if they all equal each other and red if they do not. Here is an example: C43 the value is $10.00 C45 the value is $10.00 C49 the value is $10.00 If all three equal each other then I want all three cells to turn green. If one or more of the values do not match then I want all three to turn red |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formulas
Does it matter if C43 is an autosum of C40, C41, C42 and C49 is an autosum of
C47 and C48? The reason I ask is that the green does not come up unless I remove the autosum on C49. "Eduardo" wrote: Hi, Go to conditional formating, new rule, the last option from the Select a rule type is the "use a formula to determine which cells to format" "Scooter" wrote: I am using Excel 2007 and when I click on conditional formatting I do not see "Formula is" "Luke M" wrote: Select all three cells (Hold Ctrl to select inidvidual cells) goto: Format-Conditional Format. Change first box to "Formula is" Input: =AND($C$43=$C$45,$C$43=$C$49) Click format, go to pattern tab, and select green. Hit 'ok' to close dialogue. Now, with all 3 cells still selected, format the cells red (as you would normally). Now, your cells will be red by default, unless they meet your criteria (as you desired). -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Scooter" wrote: I have three cells that i would like to be green if they all equal each other and red if they do not. Here is an example: C43 the value is $10.00 C45 the value is $10.00 C49 the value is $10.00 If all three equal each other then I want all three cells to turn green. If one or more of the values do not match then I want all three to turn red |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formulas
Does it matter if C43 has a formula (+C40+C41-C42) and C45 (I input this
value manually) and C49 has a formula (+C47-C48). The reason I ask is that it does not work if I use the formulas for C43 and C49 at the same time. If I delete the formula for C43 (then manually put the value in) and leave the formula for C49 - the cells stay red. If I leave the formula for C43 and manually put a value if for C49 they turn to green. The value for C45 is always manually put in. "Luke M" wrote: Select all three cells (Hold Ctrl to select inidvidual cells) goto: Format-Conditional Format. Change first box to "Formula is" Input: =AND($C$43=$C$45,$C$43=$C$49) Click format, go to pattern tab, and select green. Hit 'ok' to close dialogue. Now, with all 3 cells still selected, format the cells red (as you would normally). Now, your cells will be red by default, unless they meet your criteria (as you desired). -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Scooter" wrote: I have three cells that i would like to be green if they all equal each other and red if they do not. Here is an example: C43 the value is $10.00 C45 the value is $10.00 C49 the value is $10.00 If all three equal each other then I want all three cells to turn green. If one or more of the values do not match then I want all three to turn red |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formulas
It shouldn't. Do note that while you may only display 2 decimals, XL keeps
track of the entire number, so certain calculations which lead to a high decimal count could cause the formula to "fail". e.g., 0 < 0.0000000123 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Scooter" wrote: Does it matter if C43 is an autosum of C40, C41, C42 and C49 is an autosum of C47 and C48? The reason I ask is that the green does not come up unless I remove the autosum on C49. "Eduardo" wrote: Hi, Go to conditional formating, new rule, the last option from the Select a rule type is the "use a formula to determine which cells to format" "Scooter" wrote: I am using Excel 2007 and when I click on conditional formatting I do not see "Formula is" "Luke M" wrote: Select all three cells (Hold Ctrl to select inidvidual cells) goto: Format-Conditional Format. Change first box to "Formula is" Input: =AND($C$43=$C$45,$C$43=$C$49) Click format, go to pattern tab, and select green. Hit 'ok' to close dialogue. Now, with all 3 cells still selected, format the cells red (as you would normally). Now, your cells will be red by default, unless they meet your criteria (as you desired). -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Scooter" wrote: I have three cells that i would like to be green if they all equal each other and red if they do not. Here is an example: C43 the value is $10.00 C45 the value is $10.00 C49 the value is $10.00 If all three equal each other then I want all three cells to turn green. If one or more of the values do not match then I want all three to turn red |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formulas
Change your formulas to include rounding, otherwise they may not equal exactly due to the inability
of binary to accurately represent decimal values. =ROUND(C40+C41-C42,2) HTH, Bernie MS Excel MVP "Scooter" wrote in message ... Does it matter if C43 has a formula (+C40+C41-C42) and C45 (I input this value manually) and C49 has a formula (+C47-C48). The reason I ask is that it does not work if I use the formulas for C43 and C49 at the same time. If I delete the formula for C43 (then manually put the value in) and leave the formula for C49 - the cells stay red. If I leave the formula for C43 and manually put a value if for C49 they turn to green. The value for C45 is always manually put in. "Luke M" wrote: Select all three cells (Hold Ctrl to select inidvidual cells) goto: Format-Conditional Format. Change first box to "Formula is" Input: =AND($C$43=$C$45,$C$43=$C$49) Click format, go to pattern tab, and select green. Hit 'ok' to close dialogue. Now, with all 3 cells still selected, format the cells red (as you would normally). Now, your cells will be red by default, unless they meet your criteria (as you desired). -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Scooter" wrote: I have three cells that i would like to be green if they all equal each other and red if they do not. Here is an example: C43 the value is $10.00 C45 the value is $10.00 C49 the value is $10.00 If all three equal each other then I want all three cells to turn green. If one or more of the values do not match then I want all three to turn red |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formulas | Excel Discussion (Misc queries) | |||
Conditional Formulas | Excel Discussion (Misc queries) | |||
Conditional Formulas | Excel Worksheet Functions | |||
How do I set up conditional formulas | Excel Worksheet Functions | |||
Conditional formulas | Excel Discussion (Misc queries) |