Looking to loop through a column in excel and highlight fill each cell if the content matches that of another column

Question

My issue now is with my conditional formatting rules - they are working as expected up to a certain point in column W, which is where the cells should be either highlighted or passed over. I have included a set of data from column W and column Z (the reference data) where there are matches that are not being highlighted. In the Column W data I have bolded the numbers that should be highlighted.

Column Z - Z506-Z550

• 233892
• 233899
• 959460
• 156311
• 515114
• 549794
• 562793
• 372953
• 230659
• 230717
• 2051205586
• 364834
• 790760
• 334588
• 538149
• 288261
• 19326
• 267428
• Net 90
• 473853
• 3211221994
• 264556
• 260798
• 156271
• 509597
• 2211211506
• 800990
• 597593
• 431759
• 377289
• 224118
• 178966
• 276840
• 430269
• 431923
• 431986
• 547439
• 512399
• 234975
• 512203
• 602547
• 443537
• 376759
• 284287
• 608745

Column W - W1144-W1155

• 233892
• 367164
• 368384
• 344813
• 233899
• 233899
• 233895
• -
• 233917
• 284287
• 376759
• 443537

The conditional formatting formula I have is =VLOOKUP($W4,$Z4:$Z922,1,FALSE) 'Applies To' =$W$4:$W$3600

I am not sure what is causing this conditional formatting to fail here.

Ignore below - now working with conditional formatting instead of vba

I am trying to automate a manual process of cross referencing data and highlighting a cell if the contents are found anywhere in another column of data. However, the amount of data in both of these columns is not the same. And unfortunately, the column I need to loop through and check each cell often has either blank cells or cells that are dashed ("---").

I started with conditional formatting but it was not working properly so I am now on VBA.

Private Sub Workbook_Open()

Dim LastRow As Long
Range("W4").Select
LastRow = Range("W4").End(xlDown).Row
Do Until ActiveCell.Row = LastRow
   If Not IsEmpty(Application.Match(ActiveCell.Value, Range("Z:Z"), 0)) 
Then
   ActiveCell.Interior.Color = vbGreen
   ActiveCell.Offset(1, 0).Select
   End If
Loop


End Sub

Right now the code has a couple issues. It is not finding the last row correctly - when debugging it shows as 65, but should be 3,535 in the test case I'm using. Additionally, my match statement is not working, as it is highlighting every cell instead of only those whose content is found in column Z. And, it highlights up to row 410, which means my Do Until loop must be wrong as well.

After figuring out the one column I eventually need to allow checking columns AA and AB for content matches.

Thanks!

Answer 1

As mentioned in the comment, conditional formatting is the way to go. I just tried the following as a conditional format.

=VLOOKUP($A2,$C$2:$C$7,1,FALSE)

A column of numbers in A as the numbers to be cross-referenced, and a list in column C which are the numbers to be checked. It works perfectly.

Answer 2

I recommend to use Conditional Formatting. The following is just to explain what was wrong with your code:

1. Always define which worksheet you mean and avoid using Select in Excel VBA.
2. Using End(xlDown) will find the next free cell (not the last used). Instead use End(xlUp) from the very last cell of the worksheet.
3. Application.Match does not return a cell but a row number. Therefore IsEmpty does not work.

In the end something like this should work:

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("SheetName")

Dim LastRow As Long
LastRow = ws.Cells(ws.Rows.Count, "W").End(xlUp).Row

Dim MatchRow As Variant

Dim iRow As Long
For iRow = 4 To LastRow
    MatchRow = 0 'initialize
    MatchRow = Application.Match(ws.Cells(iRow, "W").Value, ws.Range("Z:Z"), 0)
    If MatchRow > 0 Then
        ws.Cells(iRow, "W").Interior.Color = vbGreen
    End If
Next iRow

Note that Contidional Formatting would be a much better solution.