Highlight 3 or more consecutive blank cells in a row but not 2 consecutive or less

[Cumaitre]

Hello guys,

I am using a time keeping file I need to monitor daily and make sure the team is recording their time in the office (company policy).

The team is working 7 days per week and we have 2 consecutive free days (blank cell in time keeping). Theoretically there should never be 3 consecutive blank spaces unless there is a legit explanation.

Is there any method to highlight the 3 consecutive blank cells in a row?

I have searched the forum and can only find details on consecutive blank cells but I need to exclude those cases where there are 2 consecutive or less.

Kind regards,
Cumaitre

 

[JoeMo]

I'm assuming the cells you want to monitor do not have formulas in them.
Suppose your cells are in col A starting in A1 (adjust the rule below to suit):
1.Select all the cells you want to monitor with your mouse.
2. Home>Conditional Formatting>New rule>Use a formula
3. Formula is:

=OR(COUNTA(OFFSET($A1,0,0,3,1))=0,COUNTA(OFFSET($A1,-1,0,3,1))=0,COUNTA(OFFSET($A1,-2,0,3,1))=0)

 

[Cumaitre]

Hi JoeMo,

I've tried it and it doesn't work.

1. The cells are part of a pivot table;
2. It starts from B7;
3. I have tested the formula and it results in these errors:

* it highlight entire rows, I need only the cells to be highlighted;
* it seems to work vertically, i need it horizontally.

Please find below an example of how it should look like:

 

[JoeMo]

I wouldn't expect it to work for the case you cite in post #3 (why not provide this detail in your initial post?). As I said in post #2 "Suppose your cells are in a column ...", but now you show us they are in a Row. See if this works:

=OR(COUNTA(OFFSET(B2,0,0,1,3))=0,COUNTA(OFFSET(B2,0,-1,1,3))=0,COUNTA(OFFSET(B2,0,-2,1,3))=0)

 

[Cumaitre]

Many thanks JoeMo

Sorry for not explaining everything in the beginning, the formula worked as requested, you rock Joe.

 

[JoeMo]

Many thanks JoeMo

Sorry for not explaining everything in the beginning, the formula worked as requested, you rock Joe.

You are welcome - thanks for the reply.