-
Vlookup in vba isn't working
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
-
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.
-
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules