Conditional formatting with formula

[nibed32]

To all of you doing a wonderful job at Mrexcel I ask this simple question :

I have a range (~100 cells in a column) that each has its own formula in CF but I'd like to check if there is at least one that is true.

I already tried the solution proposed by Mr. Pearson (Conditional Formatting) and the solution proposed by Mr. Philips (http://www.xldynamic.com/source/xld.CFConditions.html#isitmet) but neither would work because of the formula I use in the CF.

The way it works in my sheet, the data in the cells A12:A100 is compared to the data in cells B12:B100 or in the cells C12:C100 depending on the value of the cell A10. The formula I use in the CF is this one, but it is different for every cell :

=OR(AND($A$10="B";$A12>$B12);AND($A$10="C";$A12>$C12))

In a perfect world, if there is at least one cell whose formula is true, I'd like the cell A10 to be colored in red but I don't know how to do it.


Continue the great work you do here it's wonderful!

 

[skywriter]

I think this is what you want.

You would have to replace the commas with semi colons as I see that is what you use. My settings are for commas. Confirm the formula with Control Shift Enter.

=OR(IF($A$10="B",IF($A$12:$A$100>$B$12:$B$100,1)),IF($A$10="C",IF($A$12:$A$100>$C12:$C$100,1)))