In Excel 2010 compare data from columns and highlight values if different using macro and VBA

1
votes

I have two columns, from 2 worksheets. In the first worksheet a column contains a list of craftspeople assigned to a task. The second worksheet contains just a list of the craftspeople. I need to compare the craftspeople from the first worksheet and highlight a cell if its value does not match any value in the second worksheet.

We could end up with a list of say 50 craftspeople in sheet2 and they could be assigned to multiple tasks in the first sheet (there could be hundreds of tasks), so the columns will not be the same length.

When we run the Marco we want any cells with a value (one sheet 1) that does not match the values in the second sheet to highlight in red and replace the text with the statement "Incorrect Name"

I have some code which I found via one of the searches on this site or another, and have modified it, so it’s close to what I want. But it highlights the data the wrong way round, its highlighting the values which match, I want the ones that do not match to be highlighted! I've tried but have been unable to correct this - can anyone help, and also tidy up the code???

Sub CompareAndHighlight()

    Dim rng1 As Range, rng2 As Range, i As Integer, j As Integer
    For i = 1 To Sheets("workorders").Range("U" & Rows.Count).End(xlUp).Row
        Set rng1 = Sheets("workorders").Range("U" & i)
        For j = 1 To Sheets("craftspersondata").Range("A" & Rows.Count).End(xlUp).Row
            Set rng2 = Sheets("craftspersondata").Range("A" & j)
            If StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) = 0 Then
                rng1.Interior.Color = RGB(255, 0, 0)
                rng1.Value = "Incorrect Name"

            End If

            Set rng2 = Nothing
        Next j
        Set rng1 = Nothing
    Next i

End Sub

I've tried changing the following line:

If StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) = 0 Then

to: If StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) <> 0 Then

but this highlights every row in the column, so doesn't give ne the fix I need...

2
vbaexcel

2 Answers

1
votes

Try to use following code:

Sub CompareAndHighlight()

    Dim rng1 As Range, rng2 As Range, i As Integer, j As Integer
    Dim isMatch As Boolean

    For i = 2 To Sheets("workorders").Range("U" & Rows.Count).End(xlUp).Row
        isMatch = False
        Set rng1 = Sheets("workorders").Range("U" & i)
        For j = 1 To Sheets("craftspersondata").Range("A" & Rows.Count).End(xlUp).Row
            Set rng2 = Sheets("craftspersondata").Range("A" & j)
            If StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) = 0 Then
                isMatch = True
                Exit For
            End If
            Set rng2 = Nothing
        Next j

        If Not isMatch Then
            rng1.Interior.Color = RGB(255, 0, 0)
            rng1.Value = "Incorrect Name"
        End If
        Set rng1 = Nothing
    Next i

End Sub
1
votes

Why are you trying to do this with a macro - it would be far easier to use conditional formatting?

Select the column that you are checking against and then hit Ctrl + F3 to create a named range. Call it CRAFT_PEOPLE for example.

Next select the column in which you want to display the highlighting. From the Home ribbon select Conditional Formatting > New Rule > Use a Formula (last one on the list).

For the formula enter:

=ISERROR(MATCH(A1,CRAFT_PEOPLE,0))

Replace A1 with the cell reference of the active cell of your current selection - make sure it has no $'s in the reference. Next hit the formatting button and pick how you want to highlight the matches.

Click OK