Conditional Formatting Formula to highlight first "N" in range

[GregM56]

Hello Excel friends,

I had to enter 7 formulas in Conditional Formatting to accomplish what I need. I am looking to see if someone can tell me an quicker, shorter, smarter and easier way to do it.

In my sample sheet, I have a range of 7 columns (D-J) in which each cell can contain a (Y, N or NA). My goal is to find the first single letter N (representing NO) from left-to-right and execute the formatting (make the N a bold red). The Y or NA will be false and do nothing.

To do this, in column K I placed a formula to search the range and note the first column in the range that contains an N (no NA). I didn't make column D an 4, rather, since it's first in my range I made it a 1 and likewise the last column J is column 7.

I placed a conditional formatting formula in each cell (actually column).
Column D looks like this:

Please Login or Register  to view this content.

Column J looks like this:

Please Login or Register  to view this content.

Of course each column has one and you can tell that each column is associated with its column number in the code.

I'm wondering if there's a single formula that can be used instead of seven. Such as using CHOOSE($K$2,D2,E2,F2,G2,H2,I2,J2).

Thanks to anyone who considers this and offers any assistance.

Kind regards.

[AliGW]

It's easy to do with one simple conditional formatting formula.

1. First, delete all of the rules you have set up so far and column K, which is not needed..
2. Select cell D2 and open the CF dialog to create a new formula rule: =AND(D2="N",COUNTIF($D2:D2,"N")=1)
3. Set the formatting required.
4. Set the range for the rule in the Applies To box.

	A	B	C	D	E	F	G	H	I	J
1	***	Age	Ctry	A	B	C	D	E	F	G
2	Boy	Six	USA	Y	Y	Y	Y	NA	N	Y
3	Boy	Six	USA	Y	Y	Y	N	NA	N	Y
4	Boy	Six	USA	Y	N	Y	N	NA	N	Y

[GregM56]

Thank you so very much. You are quite talented.
That's exactly what I was hoping for and I hope that many others see and learn from this as I have. It's so much simpler and less busy in conditional formatting than my method of using multiple formulas.
Kind regards

[AliGW]

Thanks for the nice comment.