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
2884
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 21, 2022
 - 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
2884
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 21, 2022
 512
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