Conditional formatting with if statement

[quercus]

I am sure this is a simple solution, alas my mind hasn't yet figures it out

I am trying to apply the following conditional formatting:

Assuming data is in Row 5:

• If there is data entered in ANY of cells G5:K5 then highlight the row.
• If there is data entered in ALL cells G5:K5 then no highlighting
• If there is NO data entered in cells G5:K5 then no highlighting'

Thank you
Quercus

 

[Jonmo1]

Try
=AND(COUNTA(G5:K5)>0,COUNTA(G5:K5)<5)

 

[quercus]

Try
=AND(COUNTA(G5:K5)>0,COUNTA(G5:K5)<5)

Hi Jonmo,

Thanks for the swift response

The formula you suggested, when applied to G5:K5 will highlight the cell that has data entered. When all cells G5:K5 have data then only G5 loses it's highlight. So not quite what I was after....Does that make sense?!

Essentially, I need to flag when a cell within the range G:K does not have a value, when at least one of the other cells in this range does. If none/all of the cells have a value then no formatting applies.

Quercus

 

[Jonmo1]

Probably need to use absolute refs

=AND(COUNTA($G5:$K5)>0,COUNTA($G5:$K5)<5)

 

[quercus]

Probably need to use absolute refs

=AND(COUNTA($G5:$K5)>0,COUNTA($G5:$K5)<5)

Actually fabulous - thank you muchly