Highlight unique or duplicate on 2 sheets [Solved/Closed]

liton - Mar 10, 2012 at 06:30 PM - Latest reply:  liton
- Mar 12, 2012 at 08:09 AM
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.


See more 

2 replies

Best answer
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Mar 10, 2012 at 10:38 PM
1
Thank you
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

Thank you, venkat1926 1

Something to say? Add comment

CCM has helped 1663 users this month

Nice! Thank you very much.