Highlight unique or duplicate on 2 sheets Solved/Closed

Question

Hello, 

I have a Excel file of 2 worksheets and have a list of Usernames on column B of both worksheets. Few of the usernames appear on both sheets and I am trying to highlight them with some color. I can do it using "Conditional Formating" (highlight unique/duplicate  if both columns are on same worksheet. Is there a way to do it if it is on two or more sheets. Thanks in advance. 


Configuration: Windows 7 / Opera 9.80

venkat1926 · Answer

there is a problem in direct condition formatting of one sheet with reference to another sheet. But you can circumvaent this 

suppose data in sheet 1 is like this 

name
a
s
d
f
g
h

in sheest 2

name
a
d
f
h
k

suppose you want to color the cells in column A of sheet 2 if that name occurs in column A of sheet 1

in sheet 2 B2 copy paste this formula 
=IF(COUNTIF(Sheet1!$A$1:$A$100,Sheet2!A2)>0,"exists","no")

and copy B2 down

now select B2.in conditionfal formatting "the cell value is"
"equals" 
and type in the next small window 
"exists"
click ok, click ok

now in sheset 2 copy B2 and select B3 down and clilck 
edit-pastespecial-foramt-ok

Highlight unique or duplicate on 2 sheets

1 response

Subscribe To Our Newsletter!