Conditional FOrmatting

[CoachRobMD]

Using Mac 2011 and I am trying to conditionally format a cell's text. IF K5>H5, I want the K5 text to be in RED. I close CLASSIC for style, "Use a formula to determine which cells to format" from the pull-down menu, and CUSTOM FORMAT to choose the color of red I desired. I then entered this formula:

="IF(K5>H5)"

(also tried it without the "" marks)

But no matter what value I have in K5, either < or > H5, it ALWAYS makes the font RED. The formula doesn't generate an error, so it is a correct argument. But why is it always coming out with a red font in every case? What do I need to do to get this formula to work?

TIA.

Rob

[PFDave]

Simply:

=K5>H5

[CoachRobMD]

D'oh. THat was super simple. It works perfectly.

When would i use the "IF" argument? Can you give me a simple example? Thanks PFDave. That was very helpful.

[CoachRobMD]

D'oh. THat was super simple. It works perfectly.

When would i use the "IF" argument? Can you give me a simple example? Thanks PFDave. That was very helpful.

[Pete_UK]

With conditional formatting the IF is implied (it is "conditional" after all), so you need to build up an expression which effectively equates to TRUE or FALSE, such as:

=A1>B1

or:

=ISNUMBER(MATCH(A1,B:B,0))

or:

=COUNTIF(C:C,A1)>2

(These are just random examples, not necessarily related to your specific request).

Hope this helps.

Pete

[CoachRobMD]

Yes, it does. Thanks. What do the arguments

=COUNTIF(C:C,A1)>2 and
=ISNUMBER(MATCH(A1,B:B,0))

mean in plain English? I've never seen a ":" other than with SUM(A1:A6), for example. So, C:C after COUNTIF is unclear to me, as is B:B.

TIA.
Rob

[Pete_UK]

The colon is used to separate the start and end cell of a range. C:C or B:B just means the whole of column C (or column B).

The expression:

=COUNTIF(C:C,A1)>2

means "does the value of A1 occur in column C more than 2 times ?" in plain English. The other expression:

=ISNUMBER(MATCH(A1,B:B,0))

means "is the value of A1 present anywhere in column B ?" in English. Both of these will have an answer of Yes or No (or TRUE or FALSE).

Hope this helps.

Pete

P.S. If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[PFDave]

Glad to have helped Rob

[CoachRobMD]

Just marked as solved. Thanks for the tips. The confusing part between COUNTIF and ISNUMBER is in the former, the search range appears BEFORE the cell value A1, in the latter, it's AFTER the cell value A1. Excel has so much stuff it's amazing.

I get that COUNTIF says in column C, if A1 is there, add +1. But with the ISNUMBER, you're looking to see if A1 appears anywhere in column B, but the "0" at the end seems odd. I'll have to look that one up. This site is awesome. Thanks again to you and PFDave. You guys are rock stars.

[CoachRobMD]

Just marked as solved. Thanks for the tips. The confusing part between COUNTIF and ISNUMBER is in the former, the search range appears BEFORE the cell value A1, in the latter, it's AFTER the cell value A1. Excel has so much stuff it's amazing.

I get that COUNTIF says in column C, if A1 is there, add +1. But with the ISNUMBER, you're looking to see if A1 appears anywhere in column B, but the "0" at the end seems odd. I'll have to look that one up. This site is awesome. Thanks again to you and PFDave. You guys are rock stars.

[Pete_UK]

Strictly speaking, it is the MATCH( ... ) function which is looking to see if A1 occurs within column B - the zero at the end means "exact" match. If there is not an exact match, then the MATCH function would return an error (#N/A), but if it is found within the range it returns a number (which is the relative row number in the range), so the function is wrapped in the ISNUMBER( ... ) function in order to trap that error, i.e. if the Match function finds A1 within column B (i.e. it has returned a number), then return TRUE, otherwise FALSE.

Hope this helps.

Pete

[CoachRobMD]

Yes, it does. I tried it without the "0", and it kept coming up FALSE. So I added the 0, and it worked. Now i know why. Pretty neat.