Previous Topic Next Topic

Highlight unique or duplicate on 2 sheets

Solved/Closed
liton - Mar 10, 2012 at 06:30 PM
 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.


Related:

1 response

Answer 1 / 1
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 10, 2012 at 10:38 PM
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
1
liton
Mar 12, 2012 at 08:09 AM
Nice! Thank you very much.
0