Conditional Formulas

[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

 

[Luke M]

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).

 

[Bernie Deitrick]

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]

I am using Excel 2007 and when I click on conditional formatting I do not see
"Formula is"

 

[Eduardo]

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"

 

[Bernie Deitrick]

Scooter,

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

HTH,
Bernie
MS Excel MVP

 

[Scooter]

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.

 

[Scooter]

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]

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

 

[Bernie Deitrick]

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