Hi,
I already got a version of my own that works for now, but the larger the files get the harder it is to get the compare because off the amount of resourses the process uses.
See my code below would be nice to smoothen this code out and get it faster to work.
Function is basicly compare row 2 with row 5 and when there is a difference highlight cell.
Sub CheckAML()
Dim Report As Worksheet
Dim i As Integer, j As Integer
Dim lastRow As Integer
Set Report = Sheets("AML")
lastRow = Report.UsedRange.Rows.Count
Application.ScreenUpdating = False
For i = 2 To lastRow
For j = 2 To lastRow
If Report.Cells(i, 2).Value <> "" Then
If InStr(1, Report.Cells(j, 5).Value, Report.Cells(i, 2).Value, vbTextCompare) > 0 Then
Report.Cells(i, 2).Interior.Color = RGB(232, 232, 232)
Report.Cells(i, 2).Font.Color = RGB(0, 0, 0)
Exit For
Else
Report.Cells(i, 2).Interior.Color = RGB(255, 192, 0)
Report.Cells(i, 2).Font.Color = RGB(0, 0, 0)
End If
End If
Next j
Next i
For i = 2 To lastRow
For j = 2 To lastRow
If Report.Cells(i, 5).Value <> "" Then
If InStr(1, Report.Cells(j, 2).Value, Report.Cells(i, 5).Value, vbTextCompare) > 0 Then
Report.Cells(i, 5).Interior.Color = RGB(232, 219, 223)
Report.Cells(i, 5).Font.Color = RGB(0, 0, 0)
Exit For
Else
Report.Cells(i, 5).Interior.Color = RGB(255, 192, 0)
Report.Cells(i, 5).Font.Color = RGB(0, 0, 0)
End If
End If
Next j
Next i
Application.ScreenUpdating = True
End Sub
Bookmarks