Modify code to only put "+" in empty cell if the requirement passed and the scenario was run.

Status
Not open for further replies.

Nlhicks

Board Regular
Joined
Jan 8, 2021
Messages
56
Platform
  1. Windows
This is where the code is now I am not 100% sure what I need to do to add a condition of checking for the scenario being run. I don't think it is possible to take out the yellow highlighting since it acts as the mapping. Each requirement is mapped to a different scenario where it is verified. It is acting as logic for the template so it has to stay in place what needs to be changed is that the strPassFail needs to add the condition of taking into account the scenario. "Please Help"

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 cellEasy = cellEstelle.Offset(, 2)
Set varFindScenario = Nothing
strEasy = cellEasy.Value
Set varFindScenario = .Rows(5).Find(What:=Trim(strEasy), 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

Test Tracking Test2 adding new section.xlsm
HIJKLMNOP
5Abort_12Cold_Cut_2Hot_Cut_1Hot_Cut_3Omega_1Pharos_1Hot_Cut_2Cold_Cut_1
6D_ANT_SFTY_01+
7D_ANT_SFTY_02
8D_ANT_SFTY_03
9BITO_1010
10BITO_1011
11BITO_1012
12BITO_1013
13QNTI_01
14QNTI_02
15QNTI_03
16PLT_555
17PLT_556
18PLT_557
Sheet3


Test Tracking Test2 adding new section.xlsm
ABC
1SPS#ResultScenarios
2D_ANT_SFTY_01PassAbort_12
3D_ANT_SFTY_03FailHot_Cut_3
4BITO_1011PassPharos_1
5BITO_1012PassCold_Cut_1
6QNTI_01FailHot_Cut_1
7QNTI_02Pass
8PLT_555Pass
9PLT_556Pass
Sheet4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2:C5Cell ValueduplicatestextNO
C8Cell ValueduplicatestextNO
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,244
Office Version
  1. 365
Platform
  1. Windows
Status
Not open for further replies.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,393
Messages
5,769,824
Members
425,574
Latest member
grimeslisa

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
Top