Conditional Formatting: Highlight row except blanks-CodePudding

I have a sheet that I want to highlight the row based on the value of a drop-down menu. The drop-down has the numbers 1-20, and the row has a corresponding number value.

So if you select five from the drop-down menu, I want row 7 in the sheet to become highlighted as it has the number 5. I have done this using the following custom formula with conditional formatting:

=VLOOKUP($P$9,(ROW($A3:L22)-2),1,false)

However, in the row, I have some 'black' cells with no values. I want to avoid highlighting those cells, but I can't seem to get it to work no matter what I try. I have been attempting to use AND in conjunction with the VLOOKUP, but no dice.

=AND(VLOOKUP($P$9,(ROW($A3:L22)-2),1,false),$A3:L22<>"")

Here is a link to the sheet:

https://docs.google.com/spreadsheets/d/18DRQSXzqZGQxmn6QNTO4BUQWTzjOZexYotJY-_2t78I/copy

If anyone can figure it out, that would be amazing.

CodePudding user response：

This should be all you need for the custom CF rule:

=AND($A3=$P$9, A3<>"")

CodePudding user response：

try:

=($A3=$P$9)*(A3<>"")