# if this then that???



## JOEE1979 (Jan 4, 2023)

I need to find a code that when "Yes" is in column M, then I will be asked if I want the "unit" in column H to show up in "returned to service" section
I would like this code to run behind the scenes continuously.

Thanks


Shift Update (master).xlsmHIJKLM464UnitW/O #@ StartActivityCorrected4653265142554ElectricalYes466467468469470471472473474475476477478479480481482483484485486487488MTO STICKEREDRETURNED TO SERVICEWaiting for Paperwork48949026524915265492493494495496497Shift UpdateCells with Conditional FormattingCellConditionCell FormatStop If TrueM465:M467Expression=MOD(ROW(),2)=0textNOI465:I467Expression=MOD(ROW(),2)=0textNOH465:H467,J465:L467Expression=MOD(ROW(),2)=0textNOM468:M469Expression=MOD(ROW(),2)=0textNOI468Expression=MOD(ROW(),2)=0textNOM490:M497Expression=MOD(ROW(),2)=0textNOJ490:K506Expression=MOD(ROW(),2)=0textNOH490:H497Expression=MOD(ROW(),2)=0textNOH468,J468:L468,H470:M486,H469:L469Expression=MOD(ROW(),2)=0textNOCells with Data ValidationCellAllowCriteriaJ465:J486ListYesM465:M486ListYes,No


----------



## DanteAmor (Jan 4, 2023)

Put the following code in your sheet events:


```
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim f As Range
  Dim resp As VbMsgBoxResult
  Dim i As Long
  
  If Not Intersect(Target, Range("M:M")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "Yes" Then
      resp = MsgBox("Do you want the unit to appear in the 'returned to service' section?", _
        vbYesNo + vbQuestion)
      If resp = vbYes Then
        Set f = Range("J:K").Find("RETURNED TO SERVICE", , xlValues, xlPart, , , False)
        If Not f Is Nothing Then
          i = f.Row + 2
          Do While True
            If Range("J" & i).Value = "" Then
              Range("J" & i).Value = Range("H" & Target.Row).Value
              Exit Do
            End If
            i = i + 1
          Loop
        End If
      End If
    End If
  End If
End Sub
```

*SHEET EVENT*
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.


----------



## JOEE1979 (Jan 4, 2023)

This is part of a big worksheet with multiple pieces to the worksheet. 
would it be better to put a definitive range vs a full column?

Shift Update.xlsmHIJKLM464UnitW/O #@ StartActivityCorrected465466467468469470471472473474475476477478479480481482483484485486487488MTO STICKEREDRETURNED TO SERVICEWaiting for Paperwork489490491492493494495496497498PMA CompletedWaiting for Re-inspect499500501502503504505506Shift UpdateCells with Conditional FormattingCellConditionCell FormatStop If TrueM500:M501Expression=MOD(ROW(),2)=0textNOM465:M467Expression=MOD(ROW(),2)=0textNOI465:I467Expression=MOD(ROW(),2)=0textNOH465:H467,J465:L467Expression=MOD(ROW(),2)=0textNOM468:M469Expression=MOD(ROW(),2)=0textNOI468Expression=MOD(ROW(),2)=0textNOM502:M506Expression=MOD(ROW(),2)=0textNOM490:M497Expression=MOD(ROW(),2)=0textNOJ490:K506Expression=MOD(ROW(),2)=0textNOH500:H506Expression=MOD(ROW(),2)=0textNOH490:H497Expression=MOD(ROW(),2)=0textNOH468,J468:L468,H470:M486,H469:L469Expression=MOD(ROW(),2)=0textNOCells with Data ValidationCellAllowCriteriaJ465:J486ListYesM465:M486ListYes,No


----------



## JOEE1979 (Jan 4, 2023)

also, how would I be able to run this macro automatically?


----------



## DanteAmor (Jan 4, 2023)

JOEE1979 said:


> would it be better to put a definitive range vs a full column?


I do not understand what you mean.



JOEE1979 said:


> also, how would I be able to run this macro automatically?


The code is executed automatically every time you put a "Yes" in column M, you must follow the instructions to put the code in the events of the sheet.


----------



## JOEE1979 (Jan 4, 2023)

I have tried it, not working. Do I put it in as a new module?


----------



## DanteAmor (Jan 4, 2023)

JOEE1979 said:


> Do I put it in as a new module?


No.

*SHEET EVENT*
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

Or Press F11, press Double click on your sheet:


----------



## JOEE1979 (Jan 4, 2023)

1 more thing, I noticed that a unit can appear multiple times in "returned to service" if I put "yes" more than once. How would you stop that from happening?


----------



## DanteAmor (Jan 4, 2023)

Try:


```
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim f As Range
  Dim resp As VbMsgBoxResult
  Dim i As Long
  
  If Not Intersect(Target, Range("M:M")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "Yes" Then
      resp = MsgBox("Do you want the unit to appear in the 'returned to service' section?", _
        vbYesNo + vbQuestion)
      If resp = vbYes Then
        Set f = Range("J:K").Find("RETURNED TO SERVICE", , xlValues, xlPart, , , False)
        If Not f Is Nothing Then
          i = f.Row + 2
          Set f = Range("J:K").Find(Range("H" & Target.Row).Value, , xlValues, xlWhole, , , False)
          If Not f Is Nothing Then
            MsgBox "This unit already exists in the section."
            Exit Sub
          End If
          Do While True
            If Range("J" & i).Value = "" Then
              Range("J" & i).Value = Range("H" & Target.Row).Value
              Exit Do
            End If
            i = i + 1
          Loop
        End If
      End If
    End If
  End If
End Sub
```


----------



## JOEE1979 (Jan 4, 2023)

Worked, thank you


----------



## JOEE1979 (Jan 4, 2023)

I need to find a code that when "Yes" is in column M, then I will be asked if I want the "unit" in column H to show up in "returned to service" section
I would like this code to run behind the scenes continuously.

Thanks


Shift Update (master).xlsmHIJKLM464UnitW/O #@ StartActivityCorrected4653265142554ElectricalYes466467468469470471472473474475476477478479480481482483484485486487488MTO STICKEREDRETURNED TO SERVICEWaiting for Paperwork48949026524915265492493494495496497Shift UpdateCells with Conditional FormattingCellConditionCell FormatStop If TrueM465:M467Expression=MOD(ROW(),2)=0textNOI465:I467Expression=MOD(ROW(),2)=0textNOH465:H467,J465:L467Expression=MOD(ROW(),2)=0textNOM468:M469Expression=MOD(ROW(),2)=0textNOI468Expression=MOD(ROW(),2)=0textNOM490:M497Expression=MOD(ROW(),2)=0textNOJ490:K506Expression=MOD(ROW(),2)=0textNOH490:H497Expression=MOD(ROW(),2)=0textNOH468,J468:L468,H470:M486,H469:L469Expression=MOD(ROW(),2)=0textNOCells with Data ValidationCellAllowCriteriaJ465:J486ListYesM465:M486ListYes,No


----------



## JOEE1979 (Jan 6, 2023)

Great work for helping me out, I have another request if you dont mind.
I would like to take all the "No" under the corrected tab from column M, and put them in the correct places for the left side of the sheet.
I have highlighted in yellow what I mean.
Thank you


Shift Update.xlsmABCDEFGHIJKLM464UnitW/O #@ StartActivityCorrected465123512568EACYes466653298565boostNo467468469470471472OT473Rover474Unit #Assigned toW/O #ActivityW/O TypeStatus47512531235689inspIn Progress476856956589programTBC477653298565boost478479Shift UpdateCells with Conditional FormattingCellConditionCell FormatStop If TrueA475:F476,A478:F486Expression=MOD(ROW(),2)=0textNOM465,M467Expression=MOD(ROW(),2)=0textNOI465,I467Expression=MOD(ROW(),2)=0textNOH465,J467:L467,J465:K465,H467Expression=MOD(ROW(),2)=0textNOM468:M469Expression=MOD(ROW(),2)=0textNOI468Expression=MOD(ROW(),2)=0textNOH468,J468:L468,H470:M486,H469:L469Expression=MOD(ROW(),2)=0textNOCells with Data ValidationCellAllowCriteriaB475:B479List=EmployeeE475:E1016List=WO_TypeF475:F1016List=StatusB467List=SupervisorB468List=ShiftJ465:J479ListYesA464:C464List=HeadingM465:M479ListYes,No


----------



## DanteAmor (Monday at 4:00 PM)

Try this:


```
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim f As Range
  Dim resp As VbMsgBoxResult
  Dim i As Long
  
  If Not Intersect(Target, Range("M:M")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "Yes" Then
      resp = MsgBox("Do you want the unit to appear in the 'returned to service' section?", _
        vbYesNo + vbQuestion)
      If resp = vbYes Then
        Set f = Range("J:K").Find("RETURNED TO SERVICE", , xlValues, xlPart, , , False)
        If Not f Is Nothing Then
          i = f.Row + 2
          Set f = Range("J:K").Find(Range("H" & Target.Row).Value, , xlValues, xlWhole, , , False)
          If Not f Is Nothing Then
            MsgBox "This unit already exists in the section."
            Exit Sub
          End If
          Do While True
            If Range("J" & i).Value = "" Then
              Range("J" & i).Value = Range("H" & Target.Row).Value
              Exit Do
            End If
            i = i + 1
          Loop
        End If
      End If
      
    ElseIf Target.Value = "No" Then
      i = 475
      resp = MsgBox("Is unit being shopped?", vbYesNo + vbQuestion)
      If resp = vbYes Then
        Do While True
          If Range("A" & i).Value = "" Then
            Range("A" & i).Value = Range("H" & Target.Row).Value
            Range("C" & i).Value = Range("I" & Target.Row).Value
            Range("D" & i).Value = Range("K" & Target.Row).Value
            Exit Do
          End If
          i = i + 1
        Loop
      End If
    End If
      
  End If
End Sub
```


----------

