Select A11 (the cell with the formula in it) and click this menu item from
Excel's menu bar: Format/Conditional Formatting. On the dialog box that
appears, select "Formula Is" from the first drop down and put this formula
in the empty field that appears next to it...

=MAX(COUNTIF(A1:A10,A1:A10))2

This is the same formula, but with a test for greater than 2 added to it.
Next, click the Format button and click the Patterns tab, then select the
color you want the cell to be filled in with from the chart of colors
displayed. Finally, OK your way back to the worksheet. That should do it.
You can copy this cell (A11) to other locations and the Conditional Format
you just set up will copy with it.

--
Rick (MVP - Excel)


"John WEC" wrote in message
...
That's great Rick. I can't believe the speed of your reply.

One thing. Can I extend the formula so that it gives me a visible warning
if there are 3 or more entries the same? Like format the cell (with the
formula in) Red or something?

Thanks

"Rick Rothstein" wrote:

Let's assume the cell you want to put the formula in is A11, then put
this
array-entered** formula in A11...

=MAX(COUNTIF(A1:A10,A1:A10))

**Commit this formula with Ctrl+Shift+Enter, not just Enter by itself.

Note that all I did is specify the range covering the 10 cells above the
cell the formula is placed in. You can copy this formula anywhere (lower
than Row 10) and it will always refer to the 10 cells above it.

--
Rick (MVP - Excel)


"John WEC" wrote in message
...
Please does anyone know a formula I can enter into a cell that will
check
the
11 cells immediately above it (which all have a text entry, i.e., a
name,
and
it will be just one word, not mutliple words) to see if any name
appears
in
more than 2 of the cells? I also need to be able to copy the formula
to
lots of other cells in the same worksheet. By the way, the 11 cells
that
need to be checked have an empty cell immediately above the 11 cells.

I hope you can understand what I'm looking for here.

If you can help, I'd really appreciate it.

Thanks, John C