Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Comparing Data in Excel [Solved/Closed]

Report
Posts
2
Registration date
Sunday May 26, 2013
Status
Member
Last seen
May 27, 2013
 -
Posts
2744
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 10, 2021
 -
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
Posts
2744
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 10, 2021
 460
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
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!