highlight duplicates

dgrosen

Board Regular
Joined
May 3, 2003
Messages
110
Hi masters of the Excel spreadsheet!!! :pray:

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: :rolleyes:
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 :devilish:
Danny
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
ABCD
1
21
33
45
57
64
73
82
96
101
118
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

Board Regular
Joined
May 3, 2003
Messages
110
THANK YOU YOGI!!! THIS IS WONDERFUL!! :wink:

but it did not work!!

Thanks a lot

Danny
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,225

ADVERTISEMENT

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

Board Regular
Joined
May 3, 2003
Messages
110
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 :confused:

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 :rolleyes: (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!!! :unsure:
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

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
RSTU
1
21
33Letussayyourdatastarts
45incellS2butthenumberof
57rowsitwouldextendtochanges,
64youcansetupadynamicrangesay
73yDynRng
82=Sheet1!$S$2:INDEX(Sheet1!$S:$S,COUNTA(Sheet1!$S:$S)+ROWS(Sheet1!$1:$2)-1)
96thenapplyConditionalFormatingFormulaIs
101=COUNTIF(yDynRng,$S$2)>1
113andoptionallyusethisinyourmacroforaVBAsolution
128
Sheet1


Does it help?
 

dgrosen

Board Regular
Joined
May 3, 2003
Messages
110
Thanks for your help Yogi. But I think I am not that smart! :unsure:
Can you help giving me the whole code so I can paste it into excel.
:rolleyes: :rolleyes: :rolleyes: :rolleyes: :rolleyes: :rolleyes: :rolleyes:
Daniel
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
dgrosen said:
Thanks for your help Yogi. But I think I am not that smart! :unsure:
Can you help giving me the whole code so I can paste it into excel.
:rolleyes: :rolleyes: :rolleyes: :rolleyes: :rolleyes: :rolleyes: :rolleyes:
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top