Vlookup in vba isn't working

[kilian]

Hello everyone,

On one of my maccro on Excel I'm trying the code below that is not working. I have two columns that are almost the same in two different sheets. I want to highlight their differences with specific criterion. Everything is working well until what's in red in the code, and I see no reason why... There is no error in the code, and the red part is just not doing anything when I run the whole thing.

Thank you for helping

Cheers



Sub differences()

Dim nbRow1 As Integer
Dim nbRow2 As Integer


nbRow1 = Application.WorksheetFunction.CountA(ActiveSheet.Range("$A:$A"))
nbRow2 = Application.WorksheetFunction.CountA(Worksheets(ActiveSheet.Index + 1).Range("$A:$A"))


Set rng1 = Range("A" & 14 & ":A" & nbRow1 + 1)
Set rng2 = Worksheets(ActiveSheet.Index + 1).Range("A" & 14 & ":A" & nbRow2 + 1)
Set rngAll = Range("A" & 14 & ":Z" & nbRow1 + 1)


For Each cell In rng1
If IsError(Application.Match(cell.Value, rng2, 0)) Then
cell.Interior.ColorIndex = 15
End If
Next

For Each cell In rng2
If IsError(Application.Match(cell.Value, rng1, 0)) Then
cell.Interior.ColorIndex = 15
ElseIf "Application.VLookup(cell.Value, rngAll, 14, FALSE)" = "CLOSED" Then
cell.Interior.ColorIndex = 15
ElseIf "Application.VLookup(cell.Value, rngAll, 14, FALSE)" = "COMPLETED" Then
cell.Interior.ColorIndex = 15
End If
Next
End Sub

[Aflatoon]

Remove the quotes from around the Vlookup call. At the moment you are testing if the text "CLOSED" is equal to the text "Application.VLookup(cell.Value, rngAll, 14, FALSE)" which of course it isn't.

[kilian]

Good call, thank you