Conditional - multiple conditions

[wmtsub]

How can I set Cell A1's background to a specific color if cells B1-H1 AND J1- W1 all have values other than blank's or zero's?

 

[wmtsub]

I apoloize if I can not make myself clear. What i am in neeed of is to review all the cwlls in the range of b1 to w1 excluding I1. and if all of them contain visible data highlight A1. If ANR cell does not have a visible character then A1 should NOT be highlighted.

 

[Joe4]

If the only non-blank, non-visable characters you have are normal spaces (and not some special characters line non-breaking spaces or carriage returns or line feeds), then this formula should work:

=AND(SUMPRODUCT(--(TRIM(B1:H1)=""))=7,SUMPRODUCT(--(TRIM(J1:W1)=""))=14)

If that does not work, then you need to tell us exactly what we are dealing with here.
Where is this data coming from? Is it downloaded from the Web, or come from another program?

Identify a cell that has one of these invisible characters, and apply this formula to it and tell us what it returns (i.e. if the cell was B1):

=CODE(B1)

 

[DanteAmor]

I apoloize if I can not make myself clear. What i am in neeed of is to review all the cwlls in the range of b1 to w1 excluding I1. and if all of them contain visible data highlight A1. If ANR cell does not have a visible character then A1 should NOT be highlighted.

You tried my solution in post #6

 

[wmtsub]

Guys, I am fustrated. I can not get this to work. I opened a clean new worksheet and used Dantes forumla and it works fine. Enter it into my existing worksheet [and to be safe first delete all values in the target range and then populate the cells again wit 1-2-3-4- etc] and it does not work. Any chance if it was a table rather thatn a sheet that this would affect it?

 

[DanteAmor]

Guys, I am fustrated. I can not get this to work. I opened a clean new worksheet and used Dantes forumla and it works fine. Enter it into my existing worksheet [and to be safe first delete all values in the target range and then populate the cells again wit 1-2-3-4- etc] and it does not work. Any chance if it was a table rather thatn a sheet that this would affect it?

We ask you to provide the detail of how is your information on the sheet and so far you comment that you use a table, why?


Either way, I tried my formula in a table and it works.


As you comment, the formula works on a new sheet.


Probably there are other variants or characteristics that exist in your sheet, but as you keep them secret it is not possible that we can deliver a solution.