Excel sheets compare and color

Posted on 2012-09-12
Medium Priority
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
  • 2
LVL 50
ID: 38393585

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

ID: 38393596
I want the colors on Clar sheet only
LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 38394173
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

839 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