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
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]A1 Gill[/TD]
[TD]B1 Phillip[/TD]
[/TR]
[TR]
[TD]A2 Erik[/TD]
[TD]B2 Ricky[/TD]
[/TR]
[TR]
[TD]A3 Jack[/TD]
[TD]B3 Jason[/TD]
[/TR]
[TR]
[TD]A4 Miecky[/TD]
[TD]B4 Billy[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



SHEET 2
[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]A1 John[/TD]
[TD]B1 Gill[/TD]
[/TR]
[TR]
[TD]A2 Peter[/TD]
[TD]B2 Morgan[/TD]
[/TR]
[TR]
[TD]A3 Jones[/TD]
[TD]B3 Howard[/TD]
[/TR]
[TR]
[TD]A4 Thompson[/TD]
[TD]B4 Smith[/TD]
[/TR]
[TR]
[TD]A5 Jardine[/TD]
[TD]B5 Kivell[/TD]
[/TR]
</tbody>[/TABLE]

 

[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