LinkBack URL
About LinkBacks
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
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
Conditional Formatting
Select B7:B66
CF formula
=AND($B$6=18,$B7="")
Fill ...Black
OK
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
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.
Choose your format and voila
Farley - this is not a formula that will work in CF. A CF formula must return TRUE or FALSE.=IF(AND(B$6=18,B7=""),1,"")
See post #2 above yours for the correct approach.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
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.
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.
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
... does it ??
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.
Last edited by JohnTopley; 09-23-2021 at 11:44 AM.
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.
i think this is smilat to farley =IF(B$6=18,IF($B7="",1))=1
Thank you! It works!
Originally Posted by JohnTopley
Thank you! This works too!
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.
Choose your format and voila
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks