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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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)
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
Many thanks JoeMo

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

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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
Back
Top