Conditional Formatting - not equal to (newbie question)

[redforlife]

Hello - What I’m attempting to do is apply conditional formatting to column B that basically states: If the values in column B are not 3, 5A, 5B or 6 then highlight the cell yellow. I’ve played around with a couple of different types of formulas with no success? Any suggestions on formulas? I’ve tried using a completed formulas(<>) as well as the conditional formatting dropdown in 2010 with no luck.

[Special-K]

Select B1

Conditional Formatting
New Rule
Use a formula to determine...

=AND(B1<>3,B1<>"5A",B1<>"5B",B1<>6)

Format as Yellow
Use Format painter (paintbrush icon) to copy to other cells

NOTE: You're comparing column B against numbers and text. You may need to put the 3 and 6 in inverted commas if they are text

[Pete_UK]

Try this formula (assuming B1 is the first cell highlighted in the range):

=AND(B1<>3,B1<>"5A",B1<>"5B",B1<>6)

If the 3 and 6 are text values, they need to be enclosed in quotes, like 5A and 5B.

Hope this helps.

Pete

[redforlife]

Thanks, but for some reason it is not recognizing 5A and 5B and is conditionally formatting those values yellow. 3 and 6 seem to be working OK.

[Pete_UK]

Maybe you have some spaces in the cells that appear to contain only 5A or 5B - use =LEN(cell) in an empty cell to find out how many characters you have in the offending cell.

Hope this helps.

Pete