Change Cell Colour Based On Range of Cells

[David102938]

So I have a range of cells for a worksheet (C5:Q5 to be precise) and I want cell BY to turn red if all of those cells are blank, I know I can use Conditional Formatting to do this but I'm not sure what formula to use. Would I be able to do it for one row and then quickly do it for other rows? Or would I need to redo the process for each row, and would I be able to add in extra columns between C5 and Q5 and keep the rule?

Any help is much appreciated!

 

[Kamolga]

-click on BY5
-home tab/conditional formatting
-New rule
-last line "Use a formula to determine which cells to format"

=countblank(C5:Q5)=15

and format it in red

You can dragg it, copy-paste it or apply the format painter to "reproduce" it. I usually adapt range in conditional formatting / manage rules to have something clean (or sometimes I get 10 rules because I copied it for 10 cells)

 

[James006]

Hello,

Just select cell BY5 and use following formula for your Conditional Formatting:

=COUNTBLANK(C15:Q15)=15

Hope this will help

 

[David102938]

Hello,

Just select cell BY5 and use following formula for your Conditional Formatting:

=COUNTBLANK(C15:Q15)=15

Hope this will help

Thank you, but this changes the cell colour regardless of the contents of range C5:Q5

 

[James006]

Hello again,

Is there another condition ... on top of the 15 blanks you asked for ... in your first message ...?

 

[David102938]

Thank you, but this changes the cell colour regardless of the contents of range C5:Q5

I entered it wrong, but the cell doesn't change colour at all, could this be because cells C5:Q5 contain data validation, so aren't actually blank?

 

[James006]

The fact that cells C5:Q5 contain data validation ... does not interfere with the Conditional Formatting of your cell ... :wink:

 

[David102938]

Hello again,

Is there another condition ... on top of the 15 blanks you asked for ... in your first message ...?

There isn't another condition, I just want the cell B5 to fill in red when the cells C5:Q5 are blank (And also do this to B6,7,8, etc

 

[David102938]

I've somehow gotten this to work now, thank you for your help

 

[James006]

So,

Place your cursor in cell B5 ...

and for your conditional formatting use the formula : =COUNTBLANK(C15:Q15)=15

and format with the Red Fill as needed ...

HTH