asked on

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

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

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

bsharath

ASKER

I want the colors on Clar sheet only

ASKER CERTIFIED SOLUTION

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial