Conditional Formatting when a range of cells is blank.

[Cidona]

Dear Forum.

Wondering if someone could help me with correct conditional formatting as with the googling I have done and tried there are in various similar thread but are different to what I’m looking for and I’m having difficultly modifying to suit.

I have attached a sample workbook with a sample of what I’m looking to do. In the workbook there are two sheets. “Sheet1” is very similar to my real world scenario (just simplified for putting on the forum. “Sheet2” is basically what I’d be looking for it to look like when the correct Conditional Formatting would be used, but since I can’t get the correct Conditional Formatting I have just manually used fill to indicate the intent.

In layman’s terms (rather than Excel speak), what I’d be looking have the Conditional Formatting say is a combination of the following:
(a) For a given row if cell E is blank then don’t fill anything.
(b) For a given row if cell E is not blank and there is nothing in cells J:P of that row then fill in Red.
(c) For a given row if cell E is not blank, fill with Blue the relevant cells from J:P that have a number (derived from their formula).

So, in Sheet2 of the sample, cells J8:P8 are not filled since there is noting in cell E8.

In row 9 J9 is filled Blue because it is populated with 3 (derived from it’s formula). Similar for cells K10, L11, M18, J23, etc.

In row 16 the cells are filled Red as while there is something in cell E16, there isn’t anything in J16:P16.

Assistance with this would be greatly appreciated!

[protonLeah]

b) applied to $E$8:$P27

Please Login or Register  to view this content.

c) applied to $J$8:$P$27

Please Login or Register  to view this content.

[Cidona]

That is great Ben! Thank you very very much!! That will make for a nice useful workbook in my real world scenario.

Just a note in case someone else ends up referring to this thread looking to accomplish the same - there was just a small typo in part (b) of your response in regards to the 'applied to...' portion. Should be $E$8:$P$27 rather than $E$8:$e$P27.

Again, thank you very much!!