Colour all blank cells in a row if one cell in row has text.

[Ferrito]

Hello, New to this site so apologies if this has been covered before and I just don't know how to search for the answer.

I want to use conditional formatting to make blank cells yellow in any/all columns if there is text in column A.

For example, column A could be order numbers, column B could be client name and column C could be date due etc etc.
If column A is blank (i.e. all the rows at the bottom of the sheet) then nothing happens.
If column A has something in it (text or number), then the conditional formatting will look at all other cells in that row - if they have text in then nothing happens, but if they are blank they turn yellow.

Do I have to create a separate rule for each column? or can it be done spreadsheet wide?

Hopefully that made sense. Any help much appreciated.

 

[Joe4]

Welcome to the Board!

No, you can do this all at once.

First, select the entire range you want to apply this coloring to. Let's say it is B2:Z100.

Then, go to Conditional Formatting, choose the Formula option (last one), and write the CF formula as it applies to the FIRST cell in your selection above (if you write the formulas correctly, Excel will automatically adjust it for all other cells in that range).
So that formula should look like:

=AND($A2<>"",B2="")

and then choose your desired formatting color.

Note that we MUST put the "$" in front of column A to lock that column reference down, so that EVERY cell looks at column A in their row to see if anything is in it.

 

[Ferrito]

Welcome to the Board!

No, you can do this all at once.

First, select the entire range you want to apply this coloring to. Let's say it is B2:Z100.

Then, go to Conditional Formatting, choose the Formula option (last one), and write the CF formula as it applies to the FIRST cell in your selection above (if you write the formulas correctly, Excel will automatically adjust it for all other cells in that range).
So that formula should look like:

=AND($A2<>"",B2="")

and then choose your desired formatting color.

Note that we MUST put the "$" in front of column A to lock that column reference down, so that EVERY cell looks at column A in their row to see if anything is in it.

Thank you!

 

[Joe4]

You are welcome.
Glad I was able to help!