Urgent

not the best "fix" but...

I'm sure there's a conditional format that doesn't require an additional column added, but here's a quick fix if it's urgent :

add a column next to your names (ie new column B)
in B1 enter =countif(A:A,A1)
copy formula in B1 down to end of names data range
select A1
click on "format"
click on "conditional formatting"
in condition 1, select "formula is"
in the formula box enter =B1>1
select "format" and then "patterns"
choose a cell colour
then hide your added column

it's messy as you need to remember to increase your countif range if you add extra names

HTH
Chris

not the best "fix" but...

I'm sure there's a conditional format that doesn't require an additional column added, but here's a quick fix if it's urgent :

add a column next to your names (ie new column B)
in B1 enter =countif(A:A,A1)
copy formula in B1 down to end of names data range
select A1
click on "format"
click on "conditional formatting"
in condition 1, select "formula is"
in the formula box enter =B1>1
select "format" and then "patterns"
choose a cell colour
hit okay and okay
paste this formatting down to the end of your names range

then hide your added column

it's messy as you need to remember to increase your countif range if you add extra names

HTH
Chris

A VBA approach in addition to Chris's manual solution

If you need to do this today, then again tomorrow or whenever new names are added or downloaded (which happens a lot in the wonderful world of business), here is an automated approach. Modify the sheet name and range as needed. It assumes your data starts in A2 with a column header (such as "Name") in A1.

Sub DuplicateFontColor()
Application.ScreenUpdating = False
Dim RStart As Range
Dim REnd As Range
Set RStart = Sheets("Sheet2").Range("A1")
Set REnd = Sheets("Sheet2").Range("A2").End(xlDown)
With Range(RStart, REnd)
.Font.ColorIndex = 5
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
.SpecialCells(xlCellTypeVisible).Font.ColorIndex = xlAutomatic
End With
ActiveSheet.ShowAllData
Set RStart = Nothing
Set REnd = Nothing
Application.ScreenUpdating = True
End Sub

HTH

Tom Urtis

Re: A VBA approach in addition to Chris's manual solution

Tom :-)

is this achievable manually without adding my extra column ?

Whenever I try to copy the conditional format I place in, say, cell A1, the conditional formats of all the cells still refer to A1 as if it's been anchored absolutely

ie "formula is" : =if(countif(A:A,A1)>1) then blue

it's that A1 that won't change relatively when copied down

I'm sure I'm being thick here..... !

cheers
Chris

Re: A VBA approach in addition to Chris's manual solution

Chris,

Yes, this is achievable manually, for example:

(1) Highlight range of interest.
(2) Format > Cells > Font > blue or red or whatever.
(3) Data > Filter > Advance Filter, Filter the list in place, select Unique records only, OK.
(4) Edit > GoTo > Special > Visible Cells Only.
(5) Format > Cells > Font > black
(6) Data > Filter > Show All.

HTH

Tom Urtis

thanks Tom ! (NT)

: Tom :-) : Chris

Hi,

I followed this:

(1) Highlight range of interest.
(2) Format > Cells > Font > blue or red or whatever.
(3) Data > Filter > Advance Filter, Filter the list in place, select Unique records only, OK.
(4) Edit > GoTo > Special > Visible Cells Only.
(5) Format > Cells > Font > black
(6) Data > Filter > Show All.

but, now if i have to add a new name to the list, i would like it to automatically indicate the font in red if the cell has repeated value otherwise it should be in black.

Pls. Help.

Deepika

Deepika,

I would suggest then, that you employ the option per my post above
and have the computer do the work by carrying out your wish via VBA automation, either in a workbook open, or worksheet change, or worksheet activation event procedure. That way you don't have to think about it, nor will you need to rely on someone else to do it manually, where human error becomes a possible factor.

Tom Urtis

Hi,

select cell A1
goto format
select conditional formatting
choose "formula is" rather than "cell is"
enter the following formula :

=COUNTIF($A$1:$A$5000,INDEX($A$1:$A$5000,ROW(),1))>1

click on format and select your required format for any duplicates (ie bold, or red background)

okay out

paste your conditional format down as far as needed to include future additions

(just change the As to Bs or Cs depending on which column your info is in - also increase the 5000 if needed)

HTH
Chris

Tom, can you "audit" this method if you have a free second ? I've not really used index and row much, so this formula makes me nervous, but it does seem to work
\o/
thanks!

Chris,

This is an interesting possibility to explore, but when I tried it, it formatted the original and duplicate entries the same way. As is often the case, at issue is the approach or preference of the user, to choose between manual or automated control of data. My preference leans towards letting the computer do the work via automation, which at least for me allows me to concentrate on other things instead of worrying about remembering to do manual updates.

There are good arguments for both sides; the good thing is we usually have a choice.

Tom Urtis

select cell A1 goto format select conditional formatting choose "formula is" rather than "cell is" enter the following formula : \o/ thanks!