Excel sheets compare and color

Posted on 2012-09-12
Last Modified: 2012-09-17
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.

Question by:bsharath
    LVL 50

    Expert Comment


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

    Author Comment

    I want the colors on Clar sheet only
    LVL 50

    Accepted Solution

    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

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now