Using formula in conditional formatting not working

[GeneDi]

Hi everybody,

Can anyone put to bed the question of "Is there a bug in Excel 2007" where using a formula in conditional formatting doesn't work?

Using the Rule Type "Format only cells that contain" works fine but when using the Rule Type "Use a formula to determine which cells to format" doesn't work.

If you try to format cell C15, lets say bold and red, when it equals C14 (using: Cell Value, Between, = $C$14, and, =$C$14) works fine.

But,

If you try to format using formula =$C$14, any entry in C15 is red and bold.

Thanks,

Gene

 

[Jonmo1]

Welcome to the board..

it's not a bug.

When using a formula in conditional formatting, you have to write some kind of expression that returns true or false (or any number other than 0).
A conditional formatting formula DOES NOT assume you want to test the value of the cell the CF formula is entered in.
You have to write it explicitly.

When you use the Cell Value Is options, it DOES assume you want to test the value in the cell with the Conditional Formatting.

Simply writing =$C$14 in a conditional formatting formula in C15,
that IS NOT comparing C14 to C15.
Say you have a number 15 in C14
And your conditional formatting formula for C15 is =$C$14
That simply takes the value of C14, and your CF formula is now =14
14 IS a number other than 0, so CF considers it TRUE and formats the cell.

You would have to write

=C15=$C$14

This will now compare the value in C15 to the value in C14 and return TRUE or FALSE.


Hope that helps.

 

[p45cal]

I doubt there's a bug.
Try:
=C15=$C$14

Any CF formula should return True or False (or numeric equivalent of True/False)

 

[Marcelo Branco]

Try

=$C$15=$C$14

Using only
=$C$14
it returns TRUE if any number, except 0 (zero), exists in C14

HTH

M.

 

[GeneDi]

Thanks guys for the quick response.....I think the bug is between the keyboard and chair.