Conditional Formatting For Staff Roster

Hi Nancy,

You can update the conditional formatting formula as :-
=AND(COUNTIF($B4:$D4,"N")=3,$E4<>"SD")

See attached:- Staff Roster.xlsx

Also I am not sure about your second conditional formatting logic which you have given in second callout ... Do you want both the conditional formatting logics to be merged ? Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi Nancy,

Below are the logics which you have shared, would suggest you to combine them. thanks

If adjacent cell (B4:D4) has 3 consecutive "N", and E4=SD. OK
If adjacent cell (B4:D4) has 3 consecutive "N", and E4≠SD, conditional format the whole row to font colour red

If "DO" does not appear once in B6:H6, conditional format the whole row (A6:I6) to red

If that row does not have "DO" (that is off-day), I intend to have the font colour change to remind myself of the error.

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi Nancy,

Please respond on my post#4. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi Nancy,

I am unable to get you..

First conditional formatting logic is :-
=AND(COUNTIF($B4:$D4,"N")=3,$E4<>"SD")

Which means B4:D4 should contain N and E4 should not contain SD then Font Red

now on row 6, you want that A6:I6 should have atleast one "DO", else whole row Red ?

My questions are:-
1) do you want this second condition to work only on row 6?
2) if you want to merge then would it merge like :-

If column B to Column D is having N AND column E do not have "SD" AND column A to I should have at least one "DO", make the entire row Red

?

3) if yes, do you want font to be Red OR entire row to be in Red background ?

Regards,
DILIPandey

<click on below 'star' if this helps>

Thanks Nancy,

I have applied the conditional formatting basis :-
Below is REQUIRED, else entire row font would be Red:-

SD should be there after three consecutive "N" in column B,C,D
OR,
"DO" should appear at least once in entire row

see attachment:- Staff Roster.xlsx

I have tested this and found it working fine...


Regards,
DILIPandey

<click on below 'star' if this helps>

You are welcome Nancy..

Cheers

Request you to mark this thread as [SOLVED].. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>