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.

LVL 11
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


See attached

cheers, teylyn
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

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

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
bsharathAuthor Commented:
I want the colors on Clar sheet only
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.