Conditional Formatting based on two cells

[jamesdean1379]

I am trying to do a conditional formatting formula. I have Columns M and N. Both are either Y or N values. I need a conditional formatting that will highlight both cells in the row for M and N if M=Y and N=N. Now column M can also equal N and column N can also equal Y. I just need it to only highlight if M=Y and N=N. I have tried writing an IF or an OR but cant seem to get it to work.

Any Help is appreciated

 

[dreid1011]

Try this:

=AND(M1="Y",N1="N")

You will have to set this rule for each column individually.

 

[jamesdean1379]

Try this:

=AND(M1="Y",N1="N")

You will have to set this rule for each column individually.

Thanks but i tried this without $ and with $ on both columns and with and without the "" it did not highlight anything. I have seen this done before but cannot find where i saw it

 

[dreid1011]

The formatting worked for me. I used columns A and B for testing. So my formula was =AND(A1="Y",B1="N").

You have to set this rule once for each column. Meaning: Highlight all of column M or just the specific range you are using (ie: M10:M100), then create a new formatting rule based on formula. Use the formula above, but change it to the top cell in the range (ie:=AND(M10="Y",N10="N")). Then do the exact same for the N column.

It will not highlight both columns if you only make one rule with both columns selected at once.

 

[jamesdean1379]

The formatting worked for me. I used columns A and B for testing. So my formula was =AND(A1="Y",B1="N").

You have to set this rule once for each column. Meaning: Highlight all of column M or just the specific range you are using (ie: M10:M100), then create a new formatting rule based on formula. Use the formula above, but change it to the top cell in the range (ie:=AND(M10="Y",N10="N")). Then do the exact same for the N column.

It will not highlight both columns if you only make one rule with both columns selected at once.

Yes i did it exactly as you said

=AND(M2="Y",N2="N")

In both columns separately from M:M and N:N and did not highlight anything.

Steps i took
I went to conditional formatting, new rule, enter formula(=AND(M2="Y",N2="N")), assign formatting options (light red fill, bold black font), assign range, apply.

 

[dreid1011]

The only thing I am getting if I use the formula exactly as you have it, is that it highlights the row immediately above the two cells with Y and N.

I don't know what else to tell you, except you should set the formula to start at the top cell in the range. So, since you are selecting the whole column, your formula should be M1 and N1, not M2 and N2.

 

[jamesdean1379]

yeah that is weird, i tried with M1 and N1 and still not highlighting anything. Does it have anything to do with my workbook being .xlsm?

 

[dreid1011]

Shouldn't have anything to do with it.

Have you tried testing on a smaller range, like M1:N1 or M1:N2? I don't think either of those will affect it. The next thing I would check is the cell formatting?

Edit: I realize that none of that will make a difference I don't think.

 

[Eric W]

I have seen some issues where Conditional Formatting does not like the AND function. Try selecting column M and enter this CF formula:

=(M1="Y")*(N1="N")

then repeat for column N.

 

[jamesdean1379]

Soooooooooo i didn't realize this, but apparently when i pasted the data into my worksheet there were a lot of spaces between the "Y" and "N". So instead of the cell just being "Y" it was actually "Y " and "N " that was the issue.