Previous Topic Next Topic

Comparing Data in Excel

Solved/Closed
sointly Posts 2 Registration date Sunday May 26, 2013 Status Member Last seen May 27, 2013 - May 27, 2013 at 08:55 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - May 27, 2013 at 11:40 AM
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.

1 reply

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 545
May 27, 2013 at 11:40 AM
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
0