Conditional Formatting based on Row value count

Hi All,

Assuming that I have a table with 10 columns and 10 rows. I would like to highlight a value in a row if that value is the only value in that row.
I tried Conditional Formatting with COUNTA=1, however that does not work.

Any ideas?

Best regards,
Flash

Hello and welcome to the forum.

If your range is A2:J11, highlight A2:J11 > Conditional Formatting > New Rule > Use a formula
=SUMPRODUCT((ROW($A$2:$J$11)=ROW(A2))*(A2<>"")*(COUNTA($A2:$J2)=1))
Format: Fill color of your choice > OK > OK

Hi falcondude,

Thanks for your quick help and welcome.

The formula works for a simple table as described in my initial problem statement.
However, if I apply the same formula to a range in a pivot table (e.g. Range B5:Q29) it does not work anymore.
Any chance you know what the issue is here?

Best regards,
Flash

Did you adjust the formula in post #2 to suit the new range?

If your range is B5:Q29, you would select B5:Q29, go through the steps outlined above, then use this formula:

=SUMPRODUCT((ROW($B$5:$Q$29)=ROW(B5))*(B5<>"")*(COUNTA($B5:$Q5)=1))

Great, that works perfectly. Instead of the COUNTA per row I had Q29, that's why it didn't work.

Happy to help. Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Would you also be able to explain what the formula does in detail?

Thanks in advance.

After looking at this again, I realized that the formula can be simplified.

I would use this:
=AND(B5<>"",COUNTA($B5:$Q5)=1)

The formula starts in the upper left cell of the range that you highlighted. Since you highlighted B5:Q29, the formula starts in cell B5.

The ROW test isn't needed.