Matching Values across worksheets

Hi All,

I have a bit of a problem, I was hoping someone could give me some pointers. I've had a search through the forum, but couldn't find an exact solution (although I did get one other related problem solved.)

What I have is an Excel spreadsheet with about 9 worksheets. One for each current videogames console, e.g. nintendods, nintendowii, playstation3 etc.

Each sheet contains online merchants, such as amazon or play that sell games for that console. There is one merchant per column and in the merchants column is a list of the games for that console that they sell.

I am after some kind of script that I can run, that will look through all the values in the workbook, and search through all the other values to find any exact matching values and change the colour of both matching cells cells to red.

I have attached a cut down copy of the spreadsheet, with a few values per console and a few matching values coloured red.

Any help is greatly appreciated.

Many Thanks,
Craig

Hi CHodges, welcome to the Forum.

Please click GO ADVANCED and use the paperclip icon to post up a reasonable sample of your worksheet/data. If you can make it clear in the sample what data you're searching for on any given "instance" of running this macro and possibly mockup the desired results so we can SEE what you want, that will make it easy for us to assist.

The technique I would recommend would not be a macro. It take a bit more work to setup, but once done, operates in realtime. ANYTIME you have a game matching a cell on one sheet to ANY cell on any any other sheet, it would change to red.

First, the hard part.
1) Decide how much space to use on each sheet. Don't underestimate, but don't go "whole sheet" either as that could affect sheet performance as the sheet grows. Let's say you've decided to use 600 rows on each sheet, columns A:D. Of course you can adjust this, this is just to get you going.

2) Create a Name Range on each sheet. Click on Insert>Name>Define and create a named range for each sheet that goes like this:

Name: NintendoDS
Refers to: =nintendods!$A$2:$D$600

Name: NintenodWii
Referes to: =nintendowii!$A$2:$D$600

...etc. until a name exists for the data range on all sheets

3) Use conditional formatting to color the cells. You only need to gt the long COUNTIF() conditional formula working in cell A2 on the first sheet, then you can copy the formatting to the rest of the data range.

On the first sheet, click on A2:D600 (matches the range you've defined in the names). Click on Format > Conditional Formatting and use Condition1: Formula Is: =(COUNTIF(NintendoWii,A2) + COUNTIF(PCGames,A2) + COUNTIF(Playstation2,A2) + COUNTIF(Playstation3,A2) + COUNTIF(PSP,A2) + COUNTIF(xBox,A2) + COUNTIF(xBox360,A2)) > 0

Notice that series of COUNTIF() formulas includes every sheet except the one we're on.
Click on Format...Font: Color: Red
Click OK>OK

Now all the items on THIS sheet that match any items on the other sheets are turned red.

4) Repeat the CF process for all the other sheets. Remember, the CF formula should include a COUNTIF() for every sheet except the one you are on. Once that's done it will keep working on its own. No need to run macros or "remember" to do anything.

Attached is your sheet with all the named ranges already defined and the CF formula working in the first two sheets. See if you can apply it to the others.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)