Highlights duplicate "Only the selected value in the entire column"

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,645
Office Version
  1. 2010
Platform
  1. Windows
Using Excel 2000</SPAN></SPAN>
Hi,
</SPAN></SPAN>

I want duplicate to highlighted, but only the selected value in the entire column
</SPAN></SPAN>

The example below I tried to show different columns. In the column C if the cell C20 selected highlights all duplicates in the columns (with format white fonts, red filled, and with thin bordered around if possible). In the column D all duplicates D9, in the column E all duplicates E13, in the column F all duplicates F9, in the column G all duplicates G6.
</SPAN></SPAN>

Example data
</SPAN></SPAN>


Book1
ABCDEFGH
1
2
3
4
5PattPatt1n1n2n3
60 | 0 | 0 | 0 | 0 | 0 | 22 | 4 | 1634X
70 | 0 | 0 | 0 | 0 | 0 | 22 | 5 | 09122
80 | 0 | 0 | 0 | 0 | 0 | 22 | 5 | 01315X
90 | 0 | 0 | 0 | 0 | 1 | 10 | 6 | 11619X
100 | 0 | 0 | 0 | 0 | 1 | 11 | 5 | 120231
110 | 0 | 0 | 0 | 0 | 1 | 14 | 2 | 124261
120 | 0 | 0 | 0 | 0 | 1 | 15 | 1 | 12730X
130 | 0 | 0 | 0 | 0 | 2 | 02 | 4 | 131341
140 | 0 | 0 | 0 | 0 | 2 | 01 | 3 | 335382
150 | 0 | 0 | 0 | 0 | 2 | 02 | 4 | 13919X
160 | 0 | 0 | 0 | 1 | 0 | 13 | 4 | 04245X
170 | 0 | 0 | 0 | 1 | 0 | 12 | 4 | 146341
180 | 0 | 0 | 0 | 1 | 0 | 13 | 4 | 031681
190 | 0 | 0 | 0 | 1 | 0 | 13 | 3 | 1X
200 | 0 | 0 | 0 | 1 | 1 | 03 | 3 | 11
210 | 0 | 0 | 0 | 1 | 1 | 03 | 4 | 01
220 | 0 | 0 | 0 | 1 | 1 | 03 | 2 | 21
230 | 0 | 0 | 0 | 1 | 1 | 02 | 4 | 11
240 | 0 | 0 | 0 | 2 | 0 | 05 | 0 | 21
250 | 0 | 0 | 0 | 2 | 0 | 02 | 3 | 2X
260 | 0 | 0 | 0 | 2 | 0 | 01 | 2 | 4X
270 | 0 | 0 | 1 | 0 | 0 | 12 | 3 | 21
280 | 0 | 0 | 1 | 0 | 0 | 12 | 3 | 21
290 | 0 | 0 | 1 | 0 | 0 | 10 | 5 | 21
300 | 0 | 0 | 1 | 0 | 0 | 11 | 4 | 2X
310 | 0 | 0 | 1 | 0 | 1 | 00 | 6 | 1X
320 | 0 | 0 | 1 | 0 | 1 | 02 | 4 | 11
330 | 0 | 0 | 1 | 0 | 1 | 01 | 5 | 1X
340 | 0 | 0 | 0 | 1 | 1 | 01 | 4 | 22
350 | 0 | 0 | 0 | 1 | 1 | 04 | 1 | 22
360 | 0 | 0 | 1 | 1 | 0 | 00 | 5 | 2X
370 | 0 | 0 | 1 | 1 | 0 | 02 | 4 | 11
380 | 0 | 0 | 1 | 1 | 0 | 03 | 2 | 21
390 | 0 | 0 | 2 | 0 | 0 | 01 | 4 | 21
400 | 0 | 0 | 2 | 0 | 0 | 03 | 2 | 2X
410 | 0 | 0 | 2 | 0 | 0 | 02 | 3 | 21
420 | 0 | 0 | 0 | 1 | 1 | 03 | 2 | 21
430 | 0 | 0 | 0 | 1 | 1 | 02 | 2 | 3X
440 | 0 | 0 | 0 | 1 | 1 | 03 | 2 | 21
450 | 0 | 0 | 0 | 1 | 1 | 03 | 3 | 1X
460 | 0 | 0 | 0 | 1 | 1 | 03 | 4 | 01
470 | 0 | 0 | 0 | 1 | 1 | 02 | 3 | 21
480 | 0 | 1 | 0 | 0 | 0 | 14 | 3 | 01
490 | 0 | 1 | 0 | 0 | 0 | 11 | 4 | 21
500 | 0 | 1 | 0 | 0 | 0 | 14 | 2 | 11
510 | 0 | 1 | 0 | 0 | 0 | 13 | 1 | 31
520 | 0 | 0 | 0 | 1 | 1 | 01 | 5 | 1X
530 | 0 | 1 | 0 | 0 | 0 | 11 | 5 | 1X
540 | 0 | 1 | 0 | 0 | 0 | 12 | 4 | 11
550 | 0 | 1 | 0 | 0 | 0 | 16 | 0 | 11
Sheet1


Thank you in advance
</SPAN></SPAN>

Regards,
</SPAN>
Kishan
</SPAN></SPAN>
 
Last edited:

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Try this:-
Place code in Worksheet module
Code activated when cell in range is selected
Code:
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range

[COLOR="Navy"]If[/COLOR] Target.Count = 1 And Not Intersect(Target, Range("C:G")) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Cells(6, Target.Column), Cells(Rows.Count, Target.Column).End(xlUp))
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
                [COLOR="Navy"]If[/COLOR] Dn.Value = Target [COLOR="Navy"]Then[/COLOR]
                    [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
                        [COLOR="Navy"]Set[/COLOR] nRng = Dn
                    [COLOR="Navy"]Else[/COLOR]
                        [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
                   [COLOR="Navy"]End[/COLOR] If
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] Dn

[COLOR="Navy"]With[/COLOR] Rng
 .Font.Color = vbBlack
 .Interior.ColorIndex = xlNone
.Borders.Weight = 1
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] nRng
 .Font.Color = vbWhite
 .Interior.Color = vbRed
 .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Try this:-
Place code in Worksheet module
Code activated when cell in range is selected
Code:
Private [COLOR=navy]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR=navy]As[/COLOR] Range)
Regards Mick
MickG, I liked the "Worksheet_SelectionChange" it a brilliant solution and works fine within a specific range too. As I have some columns formatted so what happen, if by the mistake I do select the cell in that pre define formatted column it delete all the format.

So please could you make a macro? When I select a cell, and run the code highlight only in that column all duplicates.


Thank you
</SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Kishan
</SPAN></SPAN>
 
Upvote 0
If I understand correctly , try this:-
NB :- if you don't want to clear previous Red/White formatting in target column then Remove the code shown thus "####"
Code:
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range

[COLOR="Navy"]If[/COLOR] Target.Count = 1 And Not Intersect(Target, Range("C:G")) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Cells(6, Target.Column), Cells(Rows.Count, Target.Column).End(xlUp))
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
                [COLOR="Navy"]If[/COLOR] Dn.Value = Target [COLOR="Navy"]Then[/COLOR]
                    [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
                        [COLOR="Navy"]Set[/COLOR] nRng = Dn
                    [COLOR="Navy"]Else[/COLOR]
                        [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
                   [COLOR="Navy"]End[/COLOR] If
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] Dn

[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
'############
[COLOR="Navy"]With[/COLOR] Cells(6, Target.Column).Resize(Rng.Count)
    .Font.Color = vbBlack
    .Interior.ColorIndex = xlNone
    .Borders.Weight = 1
[COLOR="Navy"]End[/COLOR] With
'##############


[COLOR="Navy"]With[/COLOR] nRng
    .Font.Color = vbWhite
    .Interior.Color = vbRed
    .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
If I understand correctly , try this:-
NB :- if you don't want to clear previous Red/White formatting in target column then Remove the code shown thus "####"
Code:
Private [COLOR=navy]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
Thank you MickG, May I did not explain it correctly, here is my attempted, the code you send I modified the range C:G To C:DE among these 100 column over 60 columns are already formatted which I do not want to be modified.</SPAN></SPAN>

As the worksheet function works in the range, so if by mistake I do choose the cell in unwanted column it erase the previous format, that's why I ask you cloud it be the macro solution if possible


Thank you
</SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Kishan
</SPAN></SPAN>
 
Upvote 0
My understanding of what you want !!!
So you want some code that is run either on a Button or through the Macro dialog Box.!!

You want the code to run based on single cell in a column, and when the macro is run you want to highlight iin "Red/White/border" all duplicates of the selected value in that column.

Do you want to limit the selection area to columns C to G or C to DE or for all columns ???
 
Upvote 0
MickG, now I realise my mistake, it is because I have given multiple columns example in the post#1, I am sorry Mick for it

My understanding of what you want !!!
So you want some code that is run either on a Button or through the Macro dialog Box.!!
Through the Macro dialog Box.


You want the code to run based on single cell in a column, and when the macro is run you want to highlight iin "Red/White/border" all duplicates of the selected value in that column.
It is correct Mick

Do you want to limit the selection area to columns C to G or C to DE or for all columns ???
I want to be selected only the single column in which column the cell value is selected.
</SPAN></SPAN>


Kind Regards,
</SPAN></SPAN>
Kishan
</SPAN></SPAN>
 
Upvote 0
Try this:-
Code:
[COLOR="Navy"]Sub[/COLOR] MG21Oct24
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]If[/COLOR] Selection.Count = 1 [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Cells(6, Selection.Column), Cells(Rows.Count, Selection.Column).End(xlUp))
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
                [COLOR="Navy"]If[/COLOR] Dn.Value = Selection [COLOR="Navy"]Then[/COLOR]
                    [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
                        [COLOR="Navy"]Set[/COLOR] nRng = Dn
                    [COLOR="Navy"]Else[/COLOR]
                        [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
                   [COLOR="Navy"]End[/COLOR] If
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] Dn


[COLOR="Navy"]With[/COLOR] nRng
    .Font.Color = vbWhite
    .Interior.Color = vbRed
    .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] If

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Try this:-
Code:
[COLOR=navy]Sub[/COLOR] MG21Oct24
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, nRng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]If[/COLOR] Selection.Count = 1 [COLOR=navy]Then[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Cells(6, Selection.Column), Cells(Rows.Count, Selection.Column).End(xlUp))
            [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
                [COLOR=navy]If[/COLOR] Dn.Value = Selection [COLOR=navy]Then[/COLOR]
                    [COLOR=navy]If[/COLOR] nRng [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR]
                        [COLOR=navy]Set[/COLOR] nRng = Dn
                    [COLOR=navy]Else[/COLOR]
                        [COLOR=navy]Set[/COLOR] nRng = Union(nRng, Dn)
                   [COLOR=navy]End[/COLOR] If
                [COLOR=navy]End[/COLOR] If
            [COLOR=navy]Next[/COLOR] Dn


[COLOR=navy]With[/COLOR] nRng
    .Font.Color = vbWhite
    .Interior.Color = vbRed
    .Borders.Weight = 2
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] If

[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
MickG, Sorry for giving an incorrect example in the opening post, which has make you work multiple times. Excuse me for the trouble. </SPAN></SPAN>

It is solved now, as I needed!!
</SPAN></SPAN>

Thank you for your time and help. Have a good weekend
</SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Kishan
</SPAN></SPAN>:)
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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
Back
Top