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

Cumaitre

New Member
Joined
Mar 23, 2017
Messages
9
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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,386
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:
Code:
=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

New Member
Joined
Mar 23, 2017
Messages
9
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:

103hbep.png
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,386
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

Code:
=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

New Member
Joined
Mar 23, 2017
Messages
9
Many thanks JoeMo

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

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,386
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,168
Messages
5,594,632
Members
413,919
Latest member
ZaxAlchemist

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top