Auto Highlight Cells

[exelified]

Thanks in Advance:

The objective: Identify each mobile device user (and device) who hasn't used their device (at all) - i.e. no voice min. used and no data used over the past three consecutive months (as defined). Note: This is a monthly report so actual months change.

My column E will contain three consecutive instances of the returned value "Zero Use", sporadically, and as a result of conditions met in the formula below. Ex:

ROW COL E
281 Zero Use
282 Zero Use
283 Zero Use

The Question: In a script, how might I go about finding:

1) each 3 consecutively returned value instances of the term "Zero Use" and
2 then, once found - automatically highlight (relatively) the 3r x 4c just to the left of each instance (or in the case shown with E281) as shown in the attached image

=IF(AND(OR(A281="<12/23/2020",A281="Jan",A281="Feb"),AND(B281=0,C281=0)),"Zero Use","")=IF(AND(OR(A281="<12/23/2020",A281="Jan",A281="Feb"),AND(B281=0,C281=0)),"Zero Use","")

Note:: Formula is set for specific months but I'm thinking that doesn't have to be the case. i.e. any past tree consecutive months will satisfy the requirement.

 

[Roybzer]

Circling back around to update Roybzer: Thanks again. Strangely, you managed to get this to work but so far for me, results have been mixed - even using the same example sheet I provided. A screen shot of the "real world" sheet is attached. Can you tell where I've gone astray of your example?

The formula for the helper column in D has to be having issues with either the format of the dates in A or the contents of the cells that contain the usage.

Maybe test each part of the formula in seperate columns, checking =CELL("format", I5) and =SUM(J5:K5), and filling down a few rows to check the outputs.

 

[exelified]

No worries. Not sure where the discrepancies are, but I'll have a look.

Can you confirm the range that you're using for the conditional format? I see that the screenshot if from row 5, but the original condition was setup from row 3. Range should therefore be $I$7:$K$10000.

I'll have a look at the 'zero use' formula and get back to you.

 

[exelified]

The formula for the helper column in D has to be having issues with either the format of the dates in A or the contents of the cells that contain the usage.

I believe you may have nailed that theory. Today I discovered "irregularities" with the Data in GB column's cell contents which may be related to unsanitized source data downloaded from a certain wireless carrier's web portal and imported in. I'm seeing multiple / identical values in that column's filter selection dialog box... Sorting, filtering and then filling down same values appears to be fixing the issue. I appreciate your help with the formatting and ungrouping of the dates - meant to mention. That was huge! May be awhile before I can clean up the data and report back. Need to find a data integrity tool of some wort or find a 3rd part tool that can run through a data set to sniff out non-viable data.

 

[Roybzer]

I believe you may have nailed that theory. Today I discovered "irregularities" with the Data in GB column's cell contents which may be related to unsanitized source data downloaded from a certain wireless carrier's web portal and imported in. I'm seeing multiple / identical values in that column's filter selection dialog box... Sorting, filtering and then filling down same values appears to be fixing the issue. I appreciate your help with the formatting and ungrouping of the dates - meant to mention. That was huge! May be awhile before I can clean up the data and report back. Need to find a data integrity tool of some wort or find a 3rd part tool that can run through a data set to sniff out non-viable data.

No worries. Glad that it was of some help. Hope you mange to find a solution.