Attemping to loop data/extract/compare

[pleasehelpme]

Hi there,

I apologize for aking such a detailed question on my first post.

I have been struggling with the following for about a week.

Essentially, I am trying to get it to identify specific cells in the cost code sheet, and use them as
set codes to compare to the other sheet. If the first four characters match in other sheet, but not the
whole code, I want it to replace the code with the code from the cost code sheet and then turn the cell pink.

If the whole string matches, I just want it to turn green, and if it doesn't match, I want it to turn purple.

Please see below.

Thank you.

Sub findString2()

Dim cell As Range
Dim result As String
Dim xRange As Long, yRange As Long
Dim POAFE As Range
Dim NonPOAFE As Range
Dim PartialAFE As String
Dim i As Integer
Dim a As Integer
Dim b As Integer
Dim n As Integer
Dim Result2 As String
Dim FullAFE As String
Dim Result1(1 To 2000)
Dim wb As Workbook
Dim ProjectPO As Worksheet
Dim CostReport As Worksheet
Dim NonPO As Worksheet

With ThisWorkbook

Set wb = ActiveWorkbook
Set CostReport = wb.Sheets("Cost Report")
Set ProjectPO = wb.Sheets("ProjPO")
Set NonPO = wb.Sheets("Non-PO")
CostReport
'Application.Workbooks("create-a-macro").Worksheets("Cost Report").Range("B" & Rows.Count).End(xlUp).Row = k
Dim k As Long
Dim z As Long
Dim x As Long
MsgBox ("Cost Report=" & CostReport.Name)
k = CostReport.Range("A" & Rows.Count).End(xlUp).Row
z = ProjectPO.Range("I" & Rows.Count).End(xlUp).Row
x = NonPO.Range("K" & Rows.Count).End(xlUp).Row

'For Each cell In costrange
'MsgBox (cell.Value & "nerd")
'Next
a = 0
For i = 1 To k
PartialAFE = Left(CostReport.Range("A" & i), 4)
If PartialAFE = "" Then
IsError ("")
FullAFE = CostReport.Range("A" & i)
If FullAFE = "" Then
IsError ("")
End If
End If
On Error Resume Next
Set POAFE = ProjectPO.Range("I1:I" & z)

For Each cell In POAFE
n = n + 1
If cell.Value Like "CC" & "*" Then
            'MsgBox (cell.Value & "NOT SO DUMB")
            'End If
            
            'MsgBox (i)
    If InStr(1, cell.Value, PartialAFE) > 0 Then
    'And PartialAFE <> "" Then
                    b = b + 1
                    Result1(a + b) = n
                    If InStr(1, cell.Value, PartialAFE) > 0 And FullAFE <> cell.Value Then
                    'And PartialAFE <> "" And FullAFE <> "" Then
                            Cells(Result1(a + b), 9).Interior.ColorIndex = 38
                            result = result + cell.Address(False, False) + "-"
                            cell.Value = FullAFE
                            MsgBox (result & "confirmed and changed!")
                    Else
                            Cells(Result1(a + b), 9).Interior.ColorIndex = 43
                            n = n + 1
                    End If
            End If
            If InStr(1, cell.Value, PartialAFE) = 0 And i = 1 Then
                cell.Interior.ColorIndex = 39
                MsgBox "hey"
            End If
            If n = z Then Result1(a + b + 1) = -1
    End If
    Next cell
Next i
End With
End Sub

[Hightree]

What value has n on start?

[pleasehelpme]

i thought it defaulted to 0 or 1 but maybe i'm stupid?

[Hightree]

i thought it defaulted to 0 or 1 but maybe i'm stupid?

No, I think you are ‘t stupid, but always When I see code I looking for the initialisation of the variable they can course much trouble When you don’t. Then you always must ad a value, so you know by debugging or the value change

[snb]

Why don't you upload a sample file ?

Sub M_snb()
   Dim n As Integer
   MsgBox n
End Sub