Conditional formatting with ISBLANK and COUNTIF

[Joe90]

I’m trying to use a COUNTIF formula in conditional formatting to highlight an entire row only if the cell A1 is not blank. The issue I have is that it will only highlight up to the furthest cell in the row containing a 1. Several of the cells could contain a 1. The “1” could be in any cell on the row between columns C and SI.

The formula I have used is

=IF(ISBLANK($A$1),"",COUNTIF(C4:SI4,1))

Any help appreciated. Thanks

 

[Hui]

Try using :
=NOT(ISBLANK($A$1))

 

[Joe90]

Thanks for the reply, this only works for the cell A1 being blank or filled, it is not dependent on a 1 being positioned in any of the columns

 

[Khalid NGO]

Hi Joe,

I think you've missed locking your range:

=IF(ISBLANK($A$1),FALSE,COUNTIF($C4:$SI4,1))

Regards,

 

[Hui]

Do you want the Row highlighted when any cell is 1?
If so use =Sum($C$1:$SI$1)>0

 

[NARAYANK991]

Hi ,

A conditional formatting rule only needs to evaluate to a TRUE or FALSE result ; an IF statement is not really necessary. What you want done can be achieved by using the CF rule :

=AND($A$1<>"",COUNTIF($C4:$SI4,1)>0)

As Khalid has already mentioned , when you select a range , say C4:SI4 , and use a CF rule formula which uses that range in the formula , if you do not use the $ sign before the column references , the result will not be what you expect it to be.

Without the $ sign , Excel will change the formula as follows :

for cell C4 : =AND($A$1<>"",COUNTIF(C4:SI4,1)>0)

for cell D4 : =AND($A$1<>"",COUNTIF(D4:SJ4,1)>0)

for cell E4 : =AND($A$1<>"",COUNTIF(E4:SK4,1)>0)

and so on , which is not what you want.

Making the column references absolute references is a must.

Narayan

 

[Joe90]

Thanks Khalid & Narayank, both solutions work. As a checksum if you use the following it will only colour the whole row if the COUNTIF =1, so if two cells have a 1 inserted, the row will not be highlighted.

=IF(ISBLANK($A$1),"",COUNTIF($C4:$SI4,1)=1)

 

[Khalid NGO]

Thanks Khalid & Narayank, both solutions work. As a checksum if you use the following it will only colour the whole row if the COUNTIF =1, so if two cells have a 1 inserted, the row will not be highlighted.

=IF(ISBLANK($A$1),"",COUNTIF($C4:$SI4,1)=1)

Hi,
Actually, what Narayan Sir clarified, we really don't need IF statement here; so the CF rule would be:

=AND($A$1<>"",COUNTIF($C4:$SI4,1)=1)