For Each cl In ws1.Range("A10", ws1.Range("A" & Rows.count).End(xlUp))
If .exists(cl.Value) Then
'Check update Scheduler Comments to column A
85 If .Item(cl.Value)(14) <> "" Then
If cl.Comment Is Nothing Then cl.AddComment
If cl.Comment.Text <> .Item(cl.Value)(14) Then
On Error Resume Next
86
On Error GoTo 90
Cells(cl.Row, 1).Comment.Text Text:=.Item(cl.Value)(14)
Cells(cl.Row, 1).Comment.Shape.TextFrame.AutoSize = True
If Cells(cl.Row, 1).Comment.Shape.Width > 200 Then
Cells(cl.Row, 1).Comment.Shape.Width = 200
Cells(cl.Row, 1).Comment.Shape.Height = 60
End If
End If
End If
'Check/update Desc and add location as comment
87 If Trim(UCase(cl.Offset(, 1).Value)) <> Trim(UCase(.Item(cl.Value)(0))) Then
cl.Offset(, 1).Value = .Item(cl.Value)(0)
If .Item(cl.Value)(6) <> "" Then
If cl.Offset(, 1).Comment Is Nothing Then cl.Offset(, 1).AddComment
cl.Offset(, 1).Comment.Text Text:=.Item(cl.Value)(6)
cl.Offset(, 1).Comment.Shape.TextFrame.AutoSize = True
If cl.Offset(, 1).Comment.Shape.Width > 200 Then
cl.Offset(, 1).Comment.Shape.Width = 200
cl.Offset(, 1).Comment.Shape.Height = 60
End If
End If
cl.Offset(, 1).Interior.Color = rgbYellow
End If
'Check/Update Lead craft
If Trim(UCase(cl.Offset(, 8).Value)) <> Trim(UCase(.Item(cl.Value)(1))) Then
cl.Offset(, 8).Value = .Item(cl.Value)(1)
cl.Offset(, 8).Interior.Color = rgbYellow
End If
' Check/Update CFW
If Trim(UCase(cl.Offset(, 7).Value)) <> Trim(UCase(.Item(cl.Value)(2))) Then
cl.Offset(, 7).Value = .Item(cl.Value)(2)
cl.Offset(, 7).Interior.Color = rgbYellow
End If
' Check/Update Status
If Trim(UCase(cl.Offset(, 5).Value)) <> Trim(UCase(.Item(cl.Value)(3))) Then
cl.Offset(, 5).Value = .Item(cl.Value)(3)
cl.Offset(, 5).Interior.Color = rgbYellow
End If
'Check/Update priority
If Trim(UCase(cl.Offset(, 4).Value)) <> Trim(UCase(.Item(cl.Value)(4))) Then
cl.Offset(, 4).Value = .Item(cl.Value)(4)
cl.Offset(, 4).Interior.Color = rgbYellow
End If
' Update Sched Start
If CLng(cl.Offset(, 12).Value) <> CLng(.Item(cl.Value)(5)) Then
If .Item(cl.Value)(5) <> "" Then
If cl.Offset(, 12).Comment Is Nothing Then cl.Offset(, 12).AddComment
cl.Offset(, 12).Comment.Text Text:="Last date: " & cl.Offset(, 12).Value
cl.Offset(, 12).Comment.Shape.TextFrame.AutoSize = True
If cl.Offset(, 12).Comment.Shape.Width > 200 Then
cl.Offset(, 12).Comment.Shape.Width = 200
cl.Offset(, 12).Comment.Shape.Height = 60
End If
End If
cl.Offset(, 12).Value = .Item(cl.Value)(5)
cl.Offset(, 12).Interior.Color = rgbYellow
End If
' Update Parent ID
If Trim(UCase(cl.Offset(, 3).Value)) <> Trim(UCase(.Item(cl.Value)(7))) Then
cl.Offset(, 3).Value = .Item(cl.Value)(7)
If .Item(cl.Value)(8) <> "" Then
If cl.Offset(, 3).Comment Is Nothing Then cl.Offset(, 3).AddComment
cl.Offset(, 3).Comment.Text Text:=.Item(cl.Value)(8)
cl.Offset(, 3).Comment.Shape.TextFrame.AutoSize = True
If cl.Offset(, 3).Comment.Shape.Width > 200 Then
cl.Offset(, 3).Comment.Shape.Width = 200
cl.Offset(, 3).Comment.Shape.Height = 60
End If
End If
cl.Offset(, 3).Interior.Color = rgbYellow
End If
'Check/Update CAP Status
If Trim(UCase(cl.Offset(, 6).Value)) <> Trim(UCase(.Item(cl.Value)(9))) Then
cl.Offset(, 6).Value = .Item(cl.Value)(9)
cl.Offset(, 6).Interior.Color = rgbYellow
End If
'Check/Update SCE Status
If .Item(cl.Value)(10) = "Y" Then
''''''''''''''''''
If .Item(cl.Value)(15) <> "" Then
If cl.Offset(, 9).Comment Is Nothing Then cl.Offset(, 9).AddComment
47 If cl.Offset(, 9).Comment.Text <> .Item(cl.Value)(15) Then
48 cl.Offset(, 9).Comment.Text Text:="Target Finish: " & .Item(cl.Value)(15)
49 cl.Offset(, 9).Comment.Shape.TextFrame.AutoSize = True
50 If cl.Offset(, 9).Comment.Shape.Width > 200 Then
cl.Offset(, 9).Comment.Shape.Width = 200
cl.Offset(, 9).Comment.Shape.Height = 60
End If
''''''''''''''''
End If
End If
End If
If Trim(UCase(cl.Offset(, 9).Value)) <> Trim(UCase(.Item(cl.Value)(10))) Then
cl.Offset(, 9).Value = .Item(cl.Value)(10)
cl.Offset(, 9).Interior.Color = rgbYellow
End If
'Check/Update Hours
If Trim(UCase(cl.Offset(, 14).Value)) <> Trim(UCase(.Item(cl.Value)(13))) Then
cl.Offset(, 14).Value = .Item(cl.Value)(13)
cl.Offset(, 14).Interior.Color = rgbYellow
End If