Comparing two lists and highlighting matches

[GodzFire]

Hello everyone. I have the following scenario:

I have two tabs. In sheet A col C I have a bunch of computer names. In sheet B col B I have a bunch of computer names. What I would like is to compare the two lists, and if there is a match, that match is highlighted in the first sheet.

How would I go about this? Many thanks in advance!

 

[Fluff]

How about

Sub MatchHighlight()
   Dim Cl As Range
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   
   Set Ws1 = Sheets("SheetB")
   Set Ws2 = Sheets("SheetA")
   With CreateObject("scripting.dictionary")
      .comparemode = vbTextCompare
      For Each Cl In Ws1.Range("B2", Ws1.Range("B" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Nothing
      Next Cl
      For Each Cl In Ws2.Range("C2", Ws2.Range("C" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then Cl.Interior.Color = 45678
      Next Cl
   End With
End Sub

 

[GodzFire]

Thanks Fluff that does work, however is there a way to accomplish this as a formula and/or conditional formatting combo? This is a shared document so it's hard to open up and make edits with macros.

EDIT: Another reason is that I have some of the above that are already coloring some cells, and I need this to override or have a higher level/priority than the other ones.

 

[Fluff]

I suspect that it can be done via Cf/Formulae, but I've no idea how.
That's not my forte, I'm afraid.

 

[njimack]

CF:

=COUNTIF(SheetB!B:B,A2)>0

 

[GodzFire]

Trying njimack's method I get the following error:

 

[GodzFire]

Anyone have any ideas?

 

[njimack]

Excel Workbook
	A
1	Computer 1
2	Computer 2
3	Computer 3
4	Computer 4
5	Computer 5
6	Computer 6
7	Computer 7
8	Computer 8
9	Computer 9
10	Computer 10
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =COUNTIF(Sheet2!$A$1:$A$7,A1)>0	Abc
A2	1. / Formula is =COUNTIF(Sheet2!$A$1:$A$7,A1)>0	Abc
A3	1. / Formula is =COUNTIF(Sheet2!$A$1:$A$7,A1)>0	Abc
A4	1. / Formula is =COUNTIF(Sheet2!$A$1:$A$7,A1)>0	Abc
A5	1. / Formula is =COUNTIF(Sheet2!$A$1:$A$7,A1)>0	Abc
A6	1. / Formula is =COUNTIF(Sheet2!$A$1:$A$7,A1)>0	Abc
A7	1. / Formula is =COUNTIF(Sheet2!$A$1:$A$7,A1)>0	Abc
A8	1. / Formula is =COUNTIF(Sheet2!$A$1:$A$7,A1)>0	Abc
A9	1. / Formula is =COUNTIF(Sheet2!$A$1:$A$7,A1)>0	Abc
A10	1. / Formula is =COUNTIF(Sheet2!$A$1:$A$7,A1)>0	Abc