# Conditional formatting - fill range if all cells in range are not blank

1. ## Conditional formatting - fill range if all cells in range are not blank

Hi,

I have a table with 3 evaluation scores per agent. It looks like this (see attached):
A1 - name of the agent
A2 - score 1; A3 - score 2; A4 - score 3

B1 - name of the agent
B2 - score 1; etc.

I would like to set a conditional formatting to highlight A2:A4 if all 3 cells in this range have a score (a.k.a. are not blank). If one or more of these cells is empty (no score yet), the range will not highlight.

I tried different formulas that I found online, but they didn't quite work:
=COUNTA(A2:A4)=3
=COUNTBLANK(A2:A4)=0
=SUMPRODUCT(--(A2:A4<>""))=3
- all 3 only filled A2 but not A3 and A4, although the cond. formatting should apply for =\$A\$2:\$A\$4

Apparently I am doing something wrong, but I just can't figure out what

Do you perhaps have any idea what I'm doing wrong?

Thanks a lot!

BTW Hello, I´m new here on this forum, so hope I don't make too many mistakes. If so, please do let me know. Thanks!

Kathi

2. ## Re: Conditional formatting - fill range if all cells in range are not blank

=a2&a3&a4 <> ""

=CONCAT(A2:A4) <> ""

3. ## Re: Conditional formatting - fill range if all cells in range are not blank

Hi mehmetcik,

thanks for the formulas, but somehow these trigger the fill even when all 3 cells are blank...

However your formulas brought me to an idea that I found to be working....quite a long workaround I guess, but I take it for now :-D

I got this working:
=IF(\$A\$2<>"","filled","nope")&(IF(\$A\$3<>"","filled","nope"))&IF(\$A\$4<>"","filled","nope")="filledfilledfilled"

This however only works like intended when the references are absolute. When I change it to relative references or only "partial" absolute references ("\$A2", "A\$2", "A2") it only fills the first cell like the formulas I tried at the very beginning.

I tried your formulas too with absolute references, but that ended up in the fill of all 3 cells alreadybeing triggered if only 1 of them was not blank.

As I wrote, not exactly smooth and clear, but for now it works :-D

4. ## Re: Conditional formatting - fill range if all cells in range are not blank

Using the file attached to post #1, I applied the following rule to B2:H4
Formula:
`Please Login or Register  to view this content.`

D2:D4 and G2:G4 highlighted.
Let us know if you have any questions.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1