Highlight Cell if Contents are elsewhere in entire worksheet

**Shazz** · 04-02-2013, 06:48 AM

Good Morning all,

I have a workbook with various tabs with data in, I need a formula that will highlight a cell if the same data is elsewhere in the entire worksheet.

Example

Sheet 1 Cell A2 has AF02 EXJ
If any sheet has the same data in any of the cells it will flag up in red.

Is this possible at all?

Shazz
xx

**Fotis1991** · 04-02-2013, 06:58 AM

Hi Shazz. Do you have a list of many words, or just one or two?

**Fotis1991** · 04-02-2013, 07:04 AM

In fact i think that you can create a list of your strings that you want and name it. Let's say List.

Then in Conditional formatting rules in all your sheets to use this formula.

=COUNTIF(List,a1)

Adjust your range as you like.

**Shazz** · 04-02-2013, 07:04 AM

Hi Fotis,

They are Registration Numbers so there will be quite a few, but I will not know them until I get some paperwork through, so it is not a preset list.

Shazz

**Fotis1991** · 04-02-2013, 07:06 AM

Did you see my post#3?

**Shazz** · 04-02-2013, 07:27 AM

Yes I did and that was what I was replying to Fotis.

I do not have a set list of words, they are all registartion Numbers that I will not know until I enter them, that is why I need something that if a Reg No is typed it will flag up if it is also on any of the Sheets.

Shazz

**Fotis1991** · 04-02-2013, 07:37 AM

See an example. Type A or B or C or D in any cell in range A1:H20, in any sheet.

**Shazz** · 04-02-2013, 07:51 AM

I am sorry but this is not what I am after as I do not have a preset list of Registrations.

I might have a reg no in the spreadsheet today and not enter it again until 1 years time.

In my spreadsheet I could have anything up too 100 diffrent reg nos.

Shazz

**Fotis1991** · 04-02-2013, 07:56 AM

So, there is no condition at all and you need to highlight cells without specific condition..

Really i hope someone else to be able to help you because i am not able to follow you.

Good luck.