Finding matching values

[Nanditha]

Hi friends… I am looking at your expertise in the below.
I have 2 excel worksheets (dnd and cc5). I have a set of values (text) in column A. I need to highlight the matching values between sheet dnd (column A) and cc5 (column P) in green text in column P.
I used the below command but it only finds the values which is in A4. Rest of the row values are not getting highlighted.

Sheets(“dnd”).Select
Range(“A3”).Select

For Each cell in Test.columns(“A”).cells.specialcells(xlcelltypevisible)

selection.offset(1,0).select
Sheets(“cc5”).select
Cells.find(what:=sheets(“dnd”).range(“A4”),searchdirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

can someone help me to correct the code which is match all values in column A with cc5 sheet column P.
Thanks a lot for your help!

 

[Fluff]

Hi & welcome to MrExcel.
You can do that with conditional formatting.
Select P2 to last row in P & then in conditional formatting, new rule, use a formula & use

=MATCH(P2,dnd!$A$2:$A$1000,0)

then set the format you want.

 

[Nanditha]

Thank you for the response. But i need this as 1 part of the macro i am working on.

 

[Fluff]

Ok, how about

Sub Nanditha()
   Dim Cl As Range
   Dim Dic As Object
   
   Set Dic = CreateObject("scripting.dictionary")
   Dic.CompareMode = 1
   With Sheets("dnd")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         Dic(Cl.Value) = Empty
      Next Cl
   End With
   With Sheets("cc5")
      For Each Cl In .Range("P2", .Range("P" & Rows.Count).End(xlUp))
         If Dic.Exists(Cl.Value) Then Cl.Font.Color = rgbLimeGreen
      Next Cl
   End With
End Sub

 

[Nanditha]

This was awesome. But one problem.
The moment i defined Cl As Range. It says Cl = Nothing

so the following codes are not executed.

 

[Fluff]

I'm afraid I don't understand what you mean, Cl is already defined as range

 

[Nanditha]

I'm afraid I don't understand what you mean, Cl is already defined as range

Apologies for being unclear.. may be the attached picture might help!

 

[Fluff]

That is because Cl is Nothing at that point in the code, it only becomes something on the next line.

 

[Nanditha]

But the macro doesn’t run to the next line. It gives me Run-time error ‘424’: Object required

That is because Cl is Nothing at that point in the code, it only becomes something on the next line.

 

[Fluff]

It gives me Run-time error ‘424’: Object required

Then why didn't you say so?
Which line is highlighted when you click debug?