Shift Schedule patterns

[COLOR=#333333]Sub ForEach_Loop()[/COLOR]


Dim cell As Range


Dim mystring1 As String


Dim mystring2 As String


Dim mystring3 As String




'How many cells in worksheet have values in them?
  For Each cell In Sheets("SHIFT SCHEDULE").UsedRange.Cells
    If UCase(cell.Value) = "KN1" Then
    
    
mystring1 = String(1, "N")
 
 Range("List!E12") = mystring1
 
 Range("F13:I13") = Application.WorksheetFunction.Rept(Range("List!E12"), 1)
 
 ' Repeating O
 
 mystring2 = String(1, "O")
 
 Range("List!E13") = mystring2
 
 Range("J13:M13") = Application.WorksheetFunction.Rept(Range("List!E13"), 1)
 
 ' Repeating D
 
 mystring3 = String(1, "D")
 
 Range("List!E14") = mystring3
 
 Range("N13:Q13") = Application.WorksheetFunction.Rept(Range("List!E14"), 1)


 ' Repeating O
 
 mystring2 = String(1, "O")
 
 Range("List!E15") = mystring2
 
 Range("R13:U13") = Application.WorksheetFunction.Rept(Range("List!E15"), 1)


End If


Next cell


 [COLOR=#333333]End Sub[/COLOR][COLOR=#333333][/COLOR]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myVal As String
Dim repCounter As Long
Dim myLet As String
Dim myCol As Long
Dim myRow As Long
Dim myBool As Boolean




'Check if we should do anything
If Target.Count > 1 Then Exit Sub
myVal = Target.Value
myCol = Target.Column
myRow = Target.Row








'Must be a 3 character string starting with K
If Len(myVal) <> 3 Or UCase(Left(myVal, 1)) <> "K" Then Exit Sub
'Must be in D:AH column
If myCol < 4 Or myCol > 35 Then Exit Sub








myLet = Mid(myVal, 2, 1)
repCounter = CLng(Right(myVal, 1))








Application.ScreenUpdating = False
Application.EnableEvents = False








'Go until we reach end of row
    'Cells(myRow, myCol).Value = myLet
    Cells(myRow, myCol).Resize(, 5 - repCounter).Value = myLet
'    repCounter = repCounter + 1
    myCol = myCol + 5 - repCounter
    
    'Time to pick new item?
'    If repCounter = 5 Then
'        repCounter = 1
   Do Until myCol >= 35
     
        'Pick next letter in pattern
        Select Case myLet
        Case "N"
            myLet = "O": myBool = False
            
        Case "O"
            If Not myBool Then
             myLet = "D": myBool = True
            Else: myLet = "N": myBool = False
            End If
        
        Case "D"
            myLet = "O": myBool = True
            
        End Select
        Cells(myRow, myCol).Resize(, 4).Value = myLet
        myCol = myCol + 4
'    End If
Loop
Application.ScreenUpdating = True
Application.EnableEvents = True








End Sub