Consecutive status in period of 15 minutes

[steve87bg]

Hi can someone help me create macro which will go on all sheets in workbook and searche for consecutive "Failed Status" within the period of 15 minutes and highlight them. Here is the example

time	status
03.11.2015 07:15	Failed
03.11.2015 07:17	Failed
03.11.2015 07:19	Failed
03.11.2015 07:28	Failed
03.11.2015 07:32	Successful
03.11.2015 07:38	Failed

the first 4 rows have consecutive "Failed" status in period of 15 min and they should be highlighted.

I found some code that can be used but i need to change it.

Please Login or Register  to view this content.

here the problem here is that it should first find "Status" column and check consecutive values there and that see if its in a time of 15 min.

Thank you

[Richard Buttrey]

On the face of it you seem to be overcomplicating it.

Your code suggests that the words 'Failed' or 'Succesful' are already present in column B. I which case why not just use Conditional Formatting to detect the word 'Failed' and paint/highlight evey cell which meets the condition?

[steve87bg]

Hi Richard, the problem here is that it needs to check more than 3 Consecutive Failed statuses and check if they are in period of 15 min. Simple Conditional Formatting will not work here.

[Richard Buttrey]

Would you upload a workbook and manually add the colouring you expect to see. We can then be certain what you want. Would you also confirm that the date/time column is a real date number and not merely text. i.e. when you use =ISNUMBER(A1) does it return the value TRUE

[Richard Buttrey]

...and do you mean that the 3 successive Failed test starts afresh after a Successful result?

[steve87bg]

Here is the workbook with 3 sheet. On all sheets is highlighted everything that macro should be doing. To find more that 3 Consecutive "Failed" statuses and that that "Failed" statuses are in period of 15 min

ConsecutiveStatus.xlsx

[steve87bg]

is there any way that it omehow count the difference in "Failed" status range between first and last date in that range?

[Richard Buttrey]

I can't see an example of a Failed which is more than 15 minutes from the previous failed and which I'm guessing should not be coloured.
For instance suppose A24 had been timed 09:20 would that be blank?

Are all times supposed to be incremental and A25:A27 are typos and should be 09:mm?

[steve87bg]

Hi Richard. I just realize that it would be better just to highlight all consecutive Failed status no mater if they belong to period of 15 min or not. I think that this will make things easier. If there are more than 3 consecutive Failed status highlight entire row.

This have to be done on all sheets and it should first look for "status" column and check if there is a consecutive Failed there