Find duplicate entries and highlight in color

[sutharsan]

Hi

I have two worksheets with names. I want the dublicate name cell to be high lighted. e.g cell in Sheet1 A1 & Sheet2 B1 to be highlighted.

SHEET 1

A1 Gill	B1 Phillip
A2 Erik	B2 Ricky
A3 Jack	B3 Jason
A4 Miecky	B4 Billy

<tbody>
</tbody>

SHEET 2

A1 John	B1 Gill
A2 Peter	B2 Morgan
A3 Jones	B3 Howard
A4 Thompson	B4 Smith
A5 Jardine	B5 Kivell

<tbody>
</tbody>

 

[grizz]

you will need to name your range say sheet 1 A1:B5 = names
sheet 2 A1:B5 = mynames
highlight sheet 1 A1:B5 and go conditional format / formula is =isnumber(match($A1,mynames,0)) and format your colour
highlight sheet 2 A1:B5 and go conditional format / formula is =isnumber(match($A1,names,0)) and format your colour

 

[sutharsan]

I am still unable suceed in creating "highlight double entries in multiple sheets".

 

[grizz]

if you prefer IM me and I will see what I can do with your worksheet

 

[Marcelo Branco]

you will need to name your range say sheet 1 A1:B5 = names
sheet 2 A1:B5 = mynames
highlight sheet 1 A1:B5 and go conditional format / formula is =isnumber(match($A1,mynames,0)) and format your colour
highlight sheet 2 A1:B5 and go conditional format / formula is =isnumber(match($A1,names,0)) and format your colour

Following grizz idea of naming ranges (names and mynames) but changing the formulas to

=COUNTIF(mynames,A1)>0
and
=COUNTIF(names,A1)>0

Note: the MATCH function works only with one-dimensional ranges

M.

 

[mirabeau]

Hi

I have two worksheets with names. I want the dublicate name cell to be high lighted. e.g cell in Sheet1 A1 & Sheet2 B1 to be highlighted.

...

try a macro?

Sub highlight_dups()

Dim d1 As Object, d2 As Object, c As Range

Set d1 = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
d1.comparemode = 1: d2.comparemode = 1

For Each c In Sheets(1).UsedRange
    d1(c.Value) = 1
Next

For Each c In Sheets(2).UsedRange
    If d1(c.Value) = 1 Then c.Font.Color = vbRed
    d2(c.Value) = 1
Next

For Each c In Sheets(1).UsedRange
    If d2(c.Value) = 1 Then c.Font.Color = vbRed
Next

End Sub