Sub If_Red_Copy_to_ALLmissed()
Dim icell As Range, myRange As Range
Dim endcell As Range
Dim index As Long
Set myRange = Union(Range("D3:d80"), Range("h3:h80"), Range("l3:l80"), Range("p3:p80"), Range("t3:t80"))
For Each icell In myRange
If icell.Interior.Color = RGB(255, 204, 0) Then
With Sheets("IDO_Supplier")
indexO = .Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
.Cells(indexO, 6).Value = icell.Value
.Cells(indexO, 3).Value = icell.Offset(0, -2).Value
.Cells(indexO, 4).Value = icell.Offset(0, -1).Value
.Cells(indexO, 5).Value = "IDO/Supplier"
.Cells(indexO, 11).Value = Now()
.Cells(indexO, 2).Formula = "=WEEKNUM(NOW())-1"
.Cells(indexO, 7).Formula = "=IF(AND($B" & indexO & ">=1, $B" & indexO & "<=4), ""January:"", IF(AND($B" & indexO & ">=5, $B" & indexO & "<=8), ""February"", IF(AND($B" & indexO & ">=9, $B" & indexO & "<=13), ""March"", IF(AND($B" & indexO & ">=14, $B" & indexO & "<=17), ""April"", IF(AND($B" & indexO & ">=18, $B" & indexO & "<=21), ""May"", IF(AND($B" & indexO & ">=22, $B" & indexO & "<=26), ""June"", IF(AND($B" & indexO & ">=27, $B" & indexO & "<=30), ""July"", IF(AND($B" & indexO & ">=31, $B" & indexO & "<=34), ""August"", IF(AND($B" & indexO & ">=35, $B" & indexO & "<=39), ""September"", IF(AND($B" & indexO & ">=40, $B" & indexO & "<=43), ""October"", IF(AND($B" & indexO & ">=44, $B" & indexO & "<=47), ""November"", IF(AND($B" & indexO & ">=48, $B" & indexO & "<=52), ""December"", """"))))))))))))"
.Cells(indexO, 9).Formula = "=VLOOKUP($C" & indexO & ", enginelist!$A$2:$B$150, 2, FALSE)"
.Cells(indexO, 10).Formula = "=VLOOKUP($C" & indexO & ", enginelist!$A$2:$C$150, 3, FALSE)"
.Cells(indexO, 8).Formula = "=IF(ISNA(MATCH(D" & indexO & ",Sheet1!$C$3:$C$80,0)),"""",Sheet1!$C$1) & IF(ISNA(MATCH(D" & indexO & ",Sheet1!$G$3:$G$80,0)),"""",Sheet1!$G$1) & IF(ISNA(MATCH(D" & indexO & ",Sheet1!$K$3:$K$80,0)),"""",Sheet1!$J$1) & IF(ISNA(MATCH(D" & indexO & ",Sheet1!$O$3:$O$80,0)),"""",Sheet1!$N$1) & IF(ISNA(MATCH(D" & indexO & ",Sheet1!$S$3:$S$80,0)),"""",Sheet1!$R$1)"
index = indexO + 1
End With
End If
If icell.Interior.Color = RGB(255, 0, 255) Then
With Sheets("Clear_Assy")
indexPi = .Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
.Cells(indexPi, 6).Value = icell.Value
.Cells(indexPi, 3).Value = icell.Offset(0, -2).Value
.Cells(indexPi, 4).Value = icell.Offset(0, -1).Value
.Cells(indexPi, 5).Value = "Clear-Assy/Capacity"
.Cells(indexPi, 11).Value = Now()
.Cells(indexPi, 2).Formula = "=WEEKNUM(NOW())-1"
.Cells(indexPi, 7).Formula = "=IF(AND($B" & indexPi & ">=1, $B" & indexPi & "<=4), ""January:"", IF(AND($B" & indexPi & ">=5, $B" & indexPi & "<=8), ""February"", IF(AND($B" & indexPi & ">=9, $B" & indexPi & "<=13), ""March"", IF(AND($B" & indexPi & ">=14, $B" & indexPi & "<=17), ""April"", IF(AND($B" & indexPi & ">=18, $B" & indexPi & "<=21), ""May"", IF(AND($B" & indexPi & ">=22, $B" & indexPi & "<=26), ""June"", IF(AND($B" & indexPi & ">=27, $B" & indexPi & "<=30), ""July"", IF(AND($B" & indexPi & ">=31, $B" & indexPi & "<=34), ""August"", IF(AND($B" & indexPi & ">=35, $B" & indexPi & "<=39), ""September"", IF(AND($B" & indexPi & ">=40, $B" & indexPi & "<=43), ""October"", IF(AND($B" & indexPi & ">=44, $B" & indexPi & "<=47), ""November"", IF(AND($B" & indexPi & ">=48, $B" & indexPi & "<=52), ""December"", """"))))))))))))"
.Cells(indexPi, 9).Formula = "=VLOOKUP($C" & indexPi & ", enginelist!$A$2:$B$150, 2, FALSE)"
.Cells(indexPi, 10).Formula = "=VLOOKUP($C" & indexPi & ", enginelist!$A$2:$C$150, 3, FALSE)"
.Cells(indexPi, 8).Formula = "=IF(ISNA(MATCH(D" & indexPi & ",Sheet1!$C$3:$C$80,0)),"""",Sheet1!$C$1) & IF(ISNA(MATCH(D" & indexPi & ",Sheet1!$G$3:$G$80,0)),"""",Sheet1!$G$1) & IF(ISNA(MATCH(D" & indexPi & ",Sheet1!$K$3:$K$80,0)),"""",Sheet1!$J$1) & IF(ISNA(MATCH(D" & indexPi & ",Sheet1!$O$3:$O$80,0)),"""",Sheet1!$N$1) & IF(ISNA(MATCH(D" & indexPi & ",Sheet1!$S$3:$S$80,0)),"""",Sheet1!$R$1)"
index = indexPi + 1
End With
End If
If icell.Interior.Color = RGB(178, 161, 199) Then
With Sheets("reject_fail")
indexPu = .Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
.Cells(indexPu, 6).Value = icell.Value
.Cells(indexPu, 3).Value = icell.Offset(0, -2).Value
.Cells(indexPu, 4).Value = icell.Offset(0, -1).Value
.Cells(indexPu, 5).Value = "Test Fail/Engine Rejection"
.Cells(indexPu, 11).Value = Now()
.Cells(indexPu, 2).Formula = "=WEEKNUM(NOW())-1"
.Cells(indexPu, 7).Formula = "=IF(AND($B" & indexPu & ">=1, $B" & indexPu & "<=4), ""January:"", IF(AND($B" & indexPu & ">=5, $B" & indexPu & "<=8), ""February"", IF(AND($B" & indexPu & ">=9, $B" & indexPu & "<=13), ""March"", IF(AND($B" & indexPu & ">=14, $B" & indexPu & "<=17), ""April"", IF(AND($B" & indexPu & ">=18, $B" & indexPu & "<=21), ""May"", IF(AND($B" & indexPu & ">=22, $B" & indexPu & "<=26), ""June"", IF(AND($B" & indexPu & ">=27, $B" & indexPu & "<=30), ""July"", IF(AND($B" & indexPu & ">=31, $B" & indexPu & "<=34), ""August"", IF(AND($B" & indexPu & ">=35, $B" & indexPu & "<=39), ""September"", IF(AND($B" & indexPu & ">=40, $B" & indexPu & "<=43), ""October"", IF(AND($B" & indexPu & ">=44, $B" & indexPu & "<=47), ""November"", IF(AND($B" & indexPu & ">=48, $B" & indexPu & "<=52), ""December"", """"))))))))))))"
.Cells(indexPu, 9).Formula = "=VLOOKUP($C" & indexPu & ", enginelist!$A$2:$B$150, 2, FALSE)"
.Cells(indexPu, 10).Formula = "=VLOOKUP($C" & indexPu & ", enginelist!$A$2:$C$150, 3, FALSE)"
.Cells(indexPu, 8).Formula = "=IF(ISNA(MATCH(D" & indexPu & ",Sheet1!$C$3:$C$80,0)),"""",Sheet1!$C$1) & IF(ISNA(MATCH(D" & indexPu & ",Sheet1!$G$3:$G$80,0)),"""",Sheet1!$G$1) & IF(ISNA(MATCH(D" & indexPu & ",Sheet1!$K$3:$K$80,0)),"""",Sheet1!$J$1) & IF(ISNA(MATCH(D" & indexPu & ",Sheet1!$O$3:$O$80,0)),"""",Sheet1!$N$1) & IF(ISNA(MATCH(D" & indexPu & ",Sheet1!$S$3:$S$80,0)),"""",Sheet1!$R$1)"
index = indexPu + 1
End With
Bookmarks