highlight duplicates

dgrosen · Feb 14, 2004

Hi masters of the Excel spreadsheet!!!

ray:

I couldnt find on the site a simple VBA code or macro that I can use to highlight duplicates within one column
I will be pasting a different spreadsheet onto one template speadsheeet and I want to run a macro to highlight those duplicates, triplicates, etc, etc located on column T. (if it is possible each set of duplicates with a different color). Like this:

1
3
4
1
4
3
2
6
7
8

If it gets too complicated just one color. I will be more than happy with that as well.
I would be also cool if it can also give me a warning that duplicates were found!

Happy Valentines to you all

Danny

Yogi Anand · Feb 14, 2004

Hi Danny:

The following code uses Conditional Formating to color duplicate cells in a selection.

Code:

Sub y_dgrosen()
    Range("A2:A11").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIF($A$2:$A$11,$A2)>1"
    Selection.FormatConditions(1).Interior.ColorIndex = 36
End Sub

y040214h2a.xls

A

B

C

D

1

2

1

3

4

5

7

6

4

7

3

8

2

9

6

10

1

11

8

Sheet6

I have used one condition to color all duplicate cells with a single color. If you want to extend this further -- this should get you started.

dgrosen · Feb 14, 2004

THANK YOU YOGI!!! THIS IS WONDERFUL!! :wink:

but it did not work!!

Thanks a lot

Danny

Yogi Anand · Feb 14, 2004

Hi Danny:

How about describing what did not work? In my code I hard coded the range as A2:A11. Did you use the same range, or adjusted it in the code if you used a different range of cells as your selection?

Anyway, please post back describing what did not work -- and then let us take it from there.

Tom Urtis · Feb 14, 2004

Yogi's suggestion does work for at least part of what you are asking for, if you change this
"=COUNTIF($A$2:$A$11,$A2)>1"

to this
"=COUNTIF($T$2:$T$11,$T2)>1"
assuming your values start in row 2 of column T. Change $T$11 to $T$LastRowNumber

This will conditionally shade all values that are duplicates in one color. If you want more colors, especially if there are more than four separate sets of duplicated values, you'd need to dip into the color pallette with straight VBA (not CF), and if you have more than 56 possible duplicated sets, you'd need to get into RGB differences in shading, which at that point would not be discernible to the human eye anyway. More info from you would help as to your requirements and anticipated quantity of duplicated sets of values.

dgrosen · Feb 15, 2004

I have changed the formula to this:

Code:

Range("s2:s200").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIF($s$2:$s$200,$s2)>1"
    Selection.FormatConditions(1).Interior.ColorIndex = 36

It will select the range but not highlight the duplicates

The column I have is dynamic, with numbers changing every time. I want to be able to run a macro that will highligth those duplicates and matching numbers with the same color.

I made this code to work, but now I need

(help) by changing the colors each time it finds a duplicate.

Code:

Application.ScreenUpdating = False
Rng = Selection.Rows.Count
For i = Rng To 1 Step -1
myCheck = ActiveCell
ActiveCell.Offset(1, 0).Select
For j = 1 To i
If ActiveCell = myCheck Then
Selection.Font.Bold = True
Selection.Font.ColorIndex = 3
End If
ActiveCell.Offset(1, 0).Select
Next j
ActiveCell.Offset(-i, 0).Select
Next i
Application.ScreenUpdating = True

Thanks a lot and Happy day after valentines!!!

Yogi Anand · Feb 15, 2004

Hi Danny:

If the number of rows your data will extend to changes, you can setup a dynamic range in case you want to use the setup I have given you. Let us have a look at the following illustration ...

Book2

R

S

T

U

1

2

1

3

Letussayyourdatastarts

4

5

incellS2butthenumberof

5

7

rowsitwouldextendtochanges,

6

4

youcansetupadynamicrangesay

7

3

yDynRng

8

2

=Sheet1!$S$2:INDEX(Sheet1!$S:$S,COUNTA(Sheet1!$S:$S)+ROWS(Sheet1!$1:$2)-1)

9

6

thenapplyConditionalFormatingFormulaIs

10

1

=COUNTIF(yDynRng,$S$2)>1

11

3

andoptionallyusethisinyourmacroforaVBAsolution

12

8

Sheet1

Does it help?

dgrosen · Feb 15, 2004

Thanks for your help Yogi. But I think I am not that smart!

Can you help giving me the whole code so I can paste it into excel.

Daniel

Yogi Anand · Feb 15, 2004

dgrosen said:
Thanks for your help Yogi. But I think I am not that smart!
Can you help giving me the whole code so I can paste it into excel.

Daniel

Hi Daniel:

You can use just the formula based approach. You do not necessarily need VBA. Generally, unless you are doing something that requires you to use VBA instead of a formula based approach, a native worksheet formula based approach is prefered.

I think you need to take your time looking closely at what is being posted in response to your questions -- work with it, understand what is going on, then come back with specific questions at what worked and what did not work for you, or what is the part that you did not understand -- and then let us take it from there!

highlight duplicates

dgrosen

Board Regular

Excel Facts

Yogi Anand

MrExcel MVP

dgrosen

Board Regular

Yogi Anand

MrExcel MVP

Tom Urtis

MrExcel MVP

dgrosen

Board Regular

Yogi Anand

MrExcel MVP

dgrosen

Board Regular

Yogi Anand

MrExcel MVP

Similar threads

Forum statistics

Share this page

highlight duplicates

Board Regular

Excel Facts

MrExcel MVP

Board Regular

MrExcel MVP

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock