Comparing Data in ExcelSolved/Closed

Question

Hello, 

I need to compare data in one column to that in multiple other columns to find missing data. Column "A" is static data, and can be entered into any cell in any row in columns "N" through "X". I would like the specific data cell in column "A" filled red to indicate it does not appear in any row of any column "N" through "X". Appreciate any help you cam lend...thanks.

TrowaD · Answer

Hi Sointly,

Here is your requested code:
Sub ColorUniqueRed()
Dim lRow, x As Integer
lRow = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("A1:A" & lRow)
x = 0
    With Range("N1:X65536")
        Set c = .Find(cell.Value, LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                x = x + 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
If x = 0 Then cell.Interior.ColorIndex = 3
Next cell

End Sub
In case you don't know here is how to implement the code:
ALT+F11 > top menu new window > Insert > Module > Paste code in big white field.
To run code:
Select correct sheet > ALT+F8 > double-click ColorUniqueRed

Best regards,
Trowa

Comparing Data in Excel

1 response

Similar discussions

Subscribe To Our Newsletter!