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

K

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
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 to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
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
MickG said:
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
Click to expand...
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
MickG said:
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
Click to expand...
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

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


MickG said:
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.
Click to expand...
It is correct Mick

MickG said:
Do you want to limit the selection area to columns C to G or C to DE or for all columns ???
Click to expand...
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
MickG said:
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
Click to expand...
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
You must log in or register to reply here.

Similar threads

N
  • Solved
Averaging every Nth cell in a column
Replies
9
Views
112
Peter_SSs
Peter_SSs
K
  • Question Question
Populate Remarks Based on Criteria
Replies
3
Views
79
Sergius
S
F
  • Solved
Location Selection to buy
Replies
7
Views
131
freddocp
F
M
  • Solved
sumifs using date and time values
Replies
9
Views
368
Asbestos_Jen
Asbestos_Jen
Seafox
  • Question Question
Glide Path Calculation
Replies
3
Views
199
RobP
R

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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