Changing code to mark requirement "+" for pass and "-" for fail under each scenario that was run and leave it blank otherwise

[Nlhicks]

Option Explicit

Sub Test2()
If ActiveSheet.Name <> "Sheet4" Then
MsgBox "Run this from the Sheet 4.", 64, "Note:"
Exit Sub
End If

Application.ScreenUpdating = False

Dim cellEstelle As Range, strEstelle$, strPassFail$
Dim varFindRequirement As Variant, lngFindRequirement&, lngNextCol&, xCol&
Dim cellEasy As Range, strEasy$
Dim varFindScenario As Variant, lngFindScenario&, lngNextRow&, xRow&

With Sheets("Sheet3")

For Each cellEstelle In Columns(1).SpecialCells(2)
Set varFindRequirement = Nothing
strEstelle = cellEstelle.Value
Set varFindRequirement = .Columns(8).Find(What:=strEstelle, LookIn:=xlFormulas, LookAt:=xlWhole)

For Each cellEasy In Columns(3).SpecialCells(2)
Set varFindScenario = Nothing
strEasy = cellEasy.Value
Set varFindScenario = .Rows(5).Find(What:=cellEasy, LookIn:=xlFormulas, LookAt:=xlWhole)


If Not varFindRequirement Is Nothing Then
lngFindRequirement = varFindRequirement.Row
If Not varFindScenario Is Nothing Then
lngFindScenario = varFindScenario.Column
'Modify thse column number boundaries as needed.
If lngFindRequirement >= 6 And lngFindRequirement <= 19 Then
If lngFindScenario >= 9 And lngFindScenario <= 16 Then

If varFindScenario.Column And cells(cellEstelle.Row, 2).Value = "Pass" Then
strPassFail = "+"
Else
strPassFail = "-"
End If

For lngFindScenario = 9 To 16
If Len(.cells(lngFindRequirement, lngFindScenario).Value) = 0 And .cells(lngFindRequirement, lngFindScenario).Interior.ColorIndex = -4142 Then .cells(lngFindRequirement, lngFindScenario).Value = strPassFail
Next lngFindScenario
End If
End If
End If
End If

Next cellEasy

Next cellEstelle

End With

Set varFindRequirement = Nothing
Application.ScreenUpdating = True
MsgBox "Completed.", , "Done."

End Sub


This is what the code above is giving me:

Test Tracking Test2 adding new section.xlsm
	H	I	J	K	L	M	N	O	P
5		Abort_12	Cold_Cut_2	Hot_Cut_1	Hot_Cut_3	Omega_1	Pharos_1	Hot_Cut_2	Cold_Cut_1
6	D_ANT_SFTY_01	+			+	+	+
7	D_ANT_SFTY_02
8	D_ANT_SFTY_03					-			-
9	BITO_1010
10	BITO_1011	+	+		+	+		+	+
11	BITO_1012		+	+			+	+
12	BITO_1013
13	QNTI_01								-
14	QNTI_02	+			+	+			+
15	QNTI_03
16	PLT_555		+	+			+	+
17	PLT_556	+			+	+			+
18	PLT_557
Sheet3

From this data:

Test Tracking Test2 adding new section.xlsm
	A	B	C
1	SPS#	Result	Scenarios
2	D_ANT_SFTY_01	Pass	Abort_12
3	D_ANT_SFTY_03	Fail	Hot_Cut_3
4	BITO_1011	Pass	Pharos_1
5	BITO_1012	Pass	Cold_Cut_1
6	QNTI_01	Fail	Hot_Cut_1
7	QNTI_02	Pass
8	PLT_555	Pass
9	PLT_556	Pass
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C5	Cell Value	duplicates	text	NO
C8	Cell Value	duplicates	text	NO

 

[Nlhicks]

Perfect Thank you very much now I am going to try it on a really big data set and see how it does do you forsee any hiccups?

 

[bobsan42]

Perfect Thank you very much now I am going to try it on a really big data set and see how it does do you forsee any hiccups?

It's not optimized for speed. You may expect app freeze.

 

[Peter_SSs]

To clarify my repeated requests for what Sheet3 was like before and after the code is run. That was to determine for sure whether the code was producing the text values in row 5 and/or the text values in column H and/or the yellow in the various cells, or whether some or all of those things were already there. Remember that you are very familiar with your workbook but we are not, and only have what you tell us or show us.

If you have large data, I think a considerably faster code can be provided but again I would like some clarification.
In all of your mini-sheets of Sheet3, the yellow cells contain no values, just colour. Is that actually the case with your real Sheet3? In other words, in developing any code, can I be sure that those yellow cells contain nothing but colour?

 

[bobsan42]

To clarify my repeated requests for what Sheet3 was like before and after the code is run. That was to determine for sure whether the code was producing the text values in row 5 and/or the text values in column H and/or the yellow in the various cells, or whether some or all of those things were already there. Remember that you are very familiar with your workbook but we are not, and only have what you tell us or show us.

If you have large data, I think a considerably faster code can be provided but again I would like some clarification.
In all of your mini-sheets of Sheet3, the yellow cells contain no values, just colour. Is that actually the case with your real Sheet3? In other words, in developing any code, can I be sure that those yellow cells contain nothing but colour?

Since i studied the code, I will try to answer.
The code does not put the text in row 5 and col 8.
It's already there. The code use these texts to locate the cell to put plus or minus.
Then if the cell has a value or a color in it - it's skipped.
The yellow also seems to be there, although I can't imagine someone will fill a large range with scattered yellow cells manually.
I still can't completely understand the idea behind this exercise.
It also seems that the scenario column is separate from the first two.

 

[Peter_SSs]

Then if the cell has a value or a color in it - it's skipped.

Again, good reason to have before/after mini-sheets rather than trying to deduce such information from (non-working) code.
Hopefully the OP can add more certainty/clarity here too.