Possibility conditional formatting within range of cells

[Kick4s]

Hello everyone,

I have some troubles with conditional formatting, see example below:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]
[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]25-Aug-2019[/TD]
[TD]26-Aug-2019[/TD]
[TD]27-Aug-2019[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]activity1[/TD]
[TD]8[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]activity2[/TD]
[TD]
[/TD]
[TD]8[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]activity3[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

My goal is to change the colour of the "activity" cell when:

"B2 to D2 is not blank

and

date B1 to D1 has expired"

From my understanding, the (start of the) formula should be:
= AND (NOT (BLANK (B2) ) ; B1<today ()="" )<today())<today="" ())<today())<today())"<today())<today())".="" but="" this="" formula="" only="" takes="" one="" cell="" in="" account.=""
< TODAY () )<today())


Is it possible to make this work with a range of cells without having to put in multiple conditional formats?

I work with the Dutch version of Excel 2010 so ';' and ',' are different than the international version, I will clarify more if needed.

Many thanks in advance!</today())
</today>

 

[Joe4]

Welcome to the Board!

The function is ISBLANK, not BLANK.

"B2 to D2 is not blank

and

date B1 to D1 has expired"

Are you saying that ALL of B2:D2 cannot be blank, and ALL of B1:D1 must be expired?
I wasn't sure if you for the condition to be met, ALL three must be met, or just any ONE must be met.

 

[Fluff]

Select A2:A4 & use this formula
=SUMPRODUCT(($B$1:$D$1<=TODAY())*(B2:D2<>""))

 

[Kick4s]

Welcome to the Board!

The function is ISBLANK, not BLANK.


Are you saying that ALL of B2:D2 cannot be blank, and ALL of B1:D1 must be expired?
I wasn't sure if you for the condition to be met, ALL three must be met, or just any ONE must be met.

Hi Joe,

Thanks for the quick response.

In this case the following conditions should be met:
- All of B2:D2 cannot be blank
- Either B1, C1 or D1 must be expired, depending of which of B2, C2 or D2 is not blank.

So for example: if B2 is not blank and B1 is expired, then change colour "activity" cell (A2).

 

[Kick4s]

Select A2:A4 & use this formula
=SUMPRODUCT(($B$1:$D$1<=TODAY())*(B2:D2<>""))

Hi Fluff,

Thanks for the quick response, I will try the formula.

 

[Kick4s]

@Fluff

The formula works, thanks! I had to make a couple of translation changes but other than that, the formula worked.

@Joe4

Thanks anyway for your help!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Kick4s]

Hi again,

I have used the formula in my Excel sheet but unfortunately something goes wrong.

If I select a range of cells and use the formula, given in previous post, the following happens (see example below):

Example:

The table below shows what happens if the formula =SUMPRODUCT(($B$1:$D$1<=TODAY())*(B2:D2<>"")) is used for the selected cells (A2:A4).

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]date[/TD]
[TD]28-Aug-2019[/TD]
[TD]29-Aug-2019[/TD]
[TD]30-Aug-2019[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]activity1[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]activity2[/TD]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]activity3[/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

Cells A2:A4 turns green, which is not what it supposed to do. Only cell A2 should turn green.

Do I have to change the formula for each row, so:

to change colour cell A3: =SUMPRODUCT(($B$1:$D$1<=TODAY())*(B3:D3<>""))
to change colour cell A4: =SUMPRODUCT(($B$1:$D$1<=TODAY())*(B4:D4<>""))

or am I missing something?

 

[Fluff]

No you don't need to change the formula, are the "blank cells" actually empty or do they contain a formula?

 

[Kick4s]

No you don't need to change the formula, are the "blank cells" actually empty or do they contain a formula?

Hi Fluff,

If you mean the "blank" cells mentioned in the formula, then yes, they are empty.

The only cells that contains a formula are the selected cells.