Conditional Formulas

  • Thread starter Thread starter Scooter
  • Start date Start date
S

Scooter

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
 
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).
 
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
 
I am using Excel 2007 and when I click on conditional formatting I do not see
"Formula is"
 
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,

In 2007, use the CF button, then choose "Highlight Cell Rules" "More Rules" "Use a formula".....

HTH,
Bernie
MS Excel MVP
 
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.
 
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.
 
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
 
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
 
Back
Top