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

Report
-
 liton -
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.


1 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
792
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Nice! Thank you very much.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!