# How to fill in blank cells with conditional formatting dependent on another cell

1. ## How to fill in blank cells with conditional formatting dependent on another cell

Hi,

I am trying to shade in the blank cells for each column (B\$7:B\$66) when the value =18 in cell (B\$6).

Sample attached below. Any help would be greatly appreciated.

Thank you.

Rowena

2. ## Re: How to fill in blank cells with conditional formatting dependent on another cell

Conditional Formatting

Select B7:B66

CF formula

=AND(\$B\$6=18,\$B7="")

Fill ...Black

OK

3. ## Re: How to fill in blank cells with conditional formatting dependent on another cell

Hi Rowena229,

If you select all cells from B6:DS66 go into conditional formatting, choose "use a formula to determine which cells to format" and type in the following formula:

Formula:
`Please Login or Register  to view this content.`

4. ## Re: How to fill in blank cells with conditional formatting dependent on another cell

=IF(AND(B\$6=18,B7=""),1,"")
Farley - this is not a formula that will work in CF. A CF formula must return TRUE or FALSE.

See post #2 above yours for the correct approach.

5. ## Re: How to fill in blank cells with conditional formatting dependent on another cell

Hi Ali,

I wrote the formula in CF to test before posting, it works.

But re-reading the OP I don't know if it will work how Rowena wants it to because it will look at each column, row 6 for the number 18 then shade in each blank one underneath. (If the CF is pasted across the whole range.)

Whereas JohnTopley's shades the whole row based on the 18 in column B.

6. ## Re: How to fill in blank cells with conditional formatting dependent on another cell

I am surprised, and suspect it isn't actually working as you think it is.

To reiterate: a CF formula rule will need to evaluate to TRUE or FALSE in order to work correctly.

7. ## Re: How to fill in blank cells with conditional formatting dependent on another cell

I always use IF formula's in CF

From the sounds of it CF must work the same way as an IF formula because IF B\$6 = 18 and the cell B7 (B7 will be dynamic when copied and pasted) is blank. Value 1 if not blank.

It will return either true or false for each cell

8. ## Re: How to fill in blank cells with conditional formatting dependent on another cell

... does it ??

9. ## Re: How to fill in blank cells with conditional formatting dependent on another cell

BOTH rules work.

If you put my rule in a cell, you will get a TRUE/FALSE condition: hence as Ali said, the IF condition is redundant in this case.

10. ## Re: How to fill in blank cells with conditional formatting dependent on another cell

It's done the same as yours but not the whole row. Like I said might not be what the OP wants but for every 18 in row 6 it will fill the blank cells in that column.

11. ## Re: How to fill in blank cells with conditional formatting dependent on another cell

i think this is smilat to farley =IF(B\$6=18,IF(\$B7="",1))=1

12. ## Re: How to fill in blank cells with conditional formatting dependent on another cell

Thank you! It works!

Originally Posted by JohnTopley
Conditional Formatting

Select B7:B66

CF formula

=AND(\$B\$6=18,\$B7="")

Fill ...Black

OK

13. ## Re: How to fill in blank cells with conditional formatting dependent on another cell

Thank you! This works too!

Originally Posted by Farley945
Hi Rowena229,

If you select all cells from B6:DS66 go into conditional formatting, choose "use a formula to determine which cells to format" and type in the following formula:

Formula:
`Please Login or Register  to view this content.`

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1