How do I auto highlight a cell which is out of spec?

[phil.rudland]

How do I auto highlight a cell which is out of spec?

I have a spreadsheet with various numbers in their cells. I have a formula to count any numbers which are +0.5 or -0.5 a certain number, but is there a way of making the cells that are counted, turn the number a different colour or 'highlight'?

This is the formula I am using:

=SUM(COUNTIF(B10:Q10,"<"&S10-0.5),COUNTIF(B10:Q10,">"&S10+0.5))

[jeffreybrown]

Take a look at Conditional Formatting

[phil.rudland]

Hi Jeff, Thanks for the link, it almost solved the problem! I need the condition to be similar to my formula, where it says of the value is +0.5 or -0.5 of Cell S10, then format that particular cell which is out of spec. Hope that makes sense?

[K m]

Assuming your value is in Cell A1

Put this in the conditional format for the cell (FORMAT/CONDITIONAL FORMATTING/FORMULA IS)

=ABS(A1-$S$10)<=0.5

[ben_hensel]

Step-wise instructions, for you, would be approximately this:

Highlight the cell range you want to format (A1:B100 or whatever).

Click "insert conditional formatting".

New Rule:
Pick "Greater than" and type in $S$10 + 0.5
Choose the format you want (red color etc)

(Another) New Rule:
Pick "Less than" and type in $S$10 - 0.5
Choose the format you want (it can be the same as above or different)

Anyway you could do this with one rule, but that would require more logic, so two clear & simple rules would probably suit your use better.

[phil.rudland]

Thanks, it worked.

[K m]

Ben: Phil's profile indicates that he is using Excel 2003. Your method won't work for Excel 2003

Absolute value accomplishes the task in one step