Conditional formatting a list and to accept blank cells

[Milos]

Hi,

I am trying to conditionally format a list to only allow a specific entries from pre-written list on another tab which this formula successfully captures:

=COUNTIF(Lists!B:B, K1) =0

However, this formula also randomly conditionally formats blank cells with that column (column K). This is a problem because I have multiple other formats on the same column which this affects. How can I change this formula to accept blank cells?

Thanks

 

[Special-K99]

You want to conditionally format only cells in column K that have a value? Yes?

Try

=AND(LEN(K1)<>0,COUNTIF(Lists!B:B, K1)=0)