Conditional Formatting

[Lucky:)]

Hi All,
I need to add conditional formatting on a range of cells.
If value in a cell different from “y” or “n” (case insensitive) the cell should be highlighted red.

I can use conditional format for “Format only cells that contain” [specific text][not containing][Y].
But it doesn’t accept OR(“Y”,”N”)

Any ideas?

Thanks.

[Colin Legg]

Welcome to the forum.

It sounds like you want the cell to be highlighted if it's value does not equal "Y" and does not equal "N".

=AND(A1<>{"Y","N"})

[Lucky:)]

Is it for "Use a formula to determine which cells to format"?

[Colin Legg]

Sure, give it a try.

[Lucky:)]

nop. it doesn't work for me.
do I have to rewrite the formula for each cell?
AND(A1<>{"Y","N"})
AND(A2<>{"Y","N"})
AND(A3<>{"Y","N"})
...

[Colin Legg]

Conditional formatting won't let you use array constants. We could use a name to work around this, but it's quite simple in this case just to make a small adjustment to the formula instead.

Let's suppose you want to apply the logic to cells C3:C20.

Select cells C3:C20.
Apply this conditional formatting formula:
=AND($C3<>"Y",$C3<>"N")


Because the row 3 isn't fixed with a $ sign, Excel will automatically adjust it for you down the column. In fact, because it's all in one column, we didn't really have to fix the column letter (it wouldn't be adjusted), but I included in the example to try to make it clearer.

[Lucky:)]

thanks. =AND($C3<>"Y",$C3<>"N") works for cells C3:C20

I tried to modify for E3:G6
=AND($E$3:$G$6<>"Y",$E$3:$G$6<>"N")
but no result. with out $ it gives me strange result - highlight whole row E:G and skips empty.