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
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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 2821 users have said thank you to us this month

Nice! Thank you very much.