Find specific text in 1 column and look for subsequent specific text in another column and format

[Smiley54665]

Hi,

I have a spreadsheet in excel 2010 that stores a list of results from a piece of equipment. If someone could give me a clue where to even start with this, I might be able to get somewhere, but I am lost. I need VBA code that will accomplish the following:

• Find any instance of “Failed” in column K
• Look for the next time that location (identified in column B) is tested again on the same date
• If that next test on the same date results in “Passed” in column K, I need the cells in A:L of the row of the “Passed” test and the initial “failed” result to have a green background color (see sample).
• This would need to work if there were multiple retests to accomplish the “Passed” result in column K (see second set of green lines on sample).

• Lastly, if there was a “failed” result in column K, but there was no follow up test on the same date that resulted in a “passed” result, I need the cells in A:L of that row to run orange.

I don’t know if this makes any sense or is even possible, but any help would be greatly appreciated. Thank you!

Here is some sample data. I couldn't figure out how to highlight the text in the sample, so I changed the color of the text instead:

PLANNAME	LOCATION	SURFACE	CHANNEL	COUNTS	PF-LIMIT	TESTNUM	DATE	TIME	OPERATOR	STATUS	SERIAL #
PLAN 1	F	SURFACE 1	LUM	505	0	1	10/18/14	20:07:26	OPERATOR 2	Failed	NL-3740
PLAN 1	E	SURFACE 1	LUM	505	0	1	10/18/14	20:07:26	OPERATOR 2	Failed	NL-3740
PLAN 1	E	SURFACE 1	LUM	0	0	1	10/18/14	20:09:25	OPERATOR 2	Passed	NL-3740
PLAN 1	B	SURFACE 1	LUM	0	0	1	10/17/14	22:09:31	OPERATOR 2	Passed	NL-3740
PLAN 1	C	SURFACE 1	LUM	0	0	1	10/17/14	22:10:21	OPERATOR 2	Passed	NL-3740
PLAN 1	D	SURFACE 1	LUM	1591	0	1	10/17/14	22:11:53	OPERATOR 2	Failed	NL-3740
PLAN 1	D	SURFACE 1	LUM	323	0	1	10/17/14	22:15:08	OPERATOR 2	Failed	NL-3740
PLAN 1	D	SURFACE 1	LUM	0	0	1	10/17/14	22:17:01	OPERATOR 2	Passed	NL-3740

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG31Oct58
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant, K [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
  [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Array(Dn, Dn.Offset(, 9).Value)
    [COLOR="Navy"]Else[/COLOR]
        Q = .Item(Dn.Value)
            [COLOR="Navy"]Set[/COLOR] Q(0) = Union(Q(0), Dn)
            Q(1) = Dn.Offset(, 9).Value
        .Item(Dn.Value) = Q
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]


 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
 [COLOR="Navy"]If[/COLOR] .Item(K)(1) = "Failed" [COLOR="Navy"]Then[/COLOR]
    .Item(K)(0).Offset(, -1).Resize(, 12).Font.ColorIndex = 46
 [COLOR="Navy"]ElseIf[/COLOR] .Item(K)(1) = "Passed" [COLOR="Navy"]Then[/COLOR]
    .Item(K)(0).Offset(, -1).Resize(, 12).Font.ColorIndex = 10
 [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick