Excel sheets compare and color

Hi All,

I have many sheets in my workbook out of which one is "clar" and one is "SEO"

Seo sheet have 5000 rows of data and column "A" has names of people

When i paste a name or click F2 and paste the name or type the name manually i want thuis name checked in all cells in Column "A" in "SEO" sheet and at same time check if the name is available in same sheet "Clar" column "A"

If in Clar sheet then color "Yellow" and if in "SEO" sheet color "Blue"
If name is new then no color

Can anyone help with a code.

Thanks
LVL 11
bsharathAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
If you enter the values on the Clar sheet, then you don't need the ClarNames range name.

Change the conditional format for the Blue color to

=COUNTIF($A:$A,$A2)>1

See attached

cheers, teylyn
27863143-v2.xlsx
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

you can do that with conditional formatting.

Create range names for the data in column A on the SEO sheet and the Clar sheet, for example

ClarNames      =OFFSET(Clar!$A$1,0,0,COUNTA(Clar!$A:$A),1)
SEONames      =OFFSET(SEO!$A$1,0,0,COUNTA(SEO!$A:$A),1)

Then you can set up two conditional formats in the data entry sheet:

 =IFERROR(MATCH($A2,ClarNames,0),FALSE)  -- BLUE
=IFERROR(MATCH($A2,SEONames,0),FALSE)  -- YELLOW

If you have an Excel Table set up, like in the attached example, you can copy and paste whole cells. If not, you should use Paste Special > Values so you don't overwrite the conditional format with pasting.

cheers, teylyn
27863143.xlsx
0
 
bsharathAuthor Commented:
I want the colors on Clar sheet only
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.