how do i highlight the values in a *filtered* list?

[bigdan]

I want take a list (which doesnt have any highlights), filter it, and then have the items that are duplicated there get highlighted.

Please see pic.

https://imgur.com/yTBncCM

In this list, at the top we can see Toronto is mentioned multiple times. That's irrelevant. If you'll look at what happens when we filter it (row 11), Toronto is only mentioned once, and Montreal is mentioned twice. So I'd like Montreal to be highlighted, not Toronto.

I do a reconciliation daily, which would involve me to filter something tens of times, maybe like 50+. So I'd really like to find something that would just take a few steps. If a solution would require me to do 4 steps for every filter, that wouldn't work for me. Of course if there's many steps involved just the first time and after that it's much more automated, that's fine.

 

[Akuini]

So I'd like Montreal to be highlighted, not Toronto.

Highlighted in which part? In the list at the top or in the filtered one? or both?
Have you tried conditional formatting? you can highlight duplicate values.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

You might also investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

 

[bigdan]

Highlighted in which part? In the list at the top or in the filtered one? or both?
Have you tried conditional formatting? you can highlight duplicate values.

highlighted in the filtered list. conditional formatting works in a non-filtered list i believe.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

You might also investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

OK noted thanks.

Btw I pressed Quote next to your comment, but it didn't show up in reply, whereas the previous persons did. I had to quote yours manually. I'm guessing the solution is simple. Do you know what I did wrong?

 

[Akuini]

It works on filtered range:

Book1
	A
1	Z
2	x
3	R
4	x
5
6	List
7	Z
8	x
10	x
16
17
18
19
20
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A6:A20	Cell Value	duplicates	text	NO

 

[Peter_SSs]

It works on filtered range:

I don't think that it does in the way the OP wants. For example here I'm thinking the OP wants Toronto highlighted in the filtered list but not Montreal.

21 08 18.xlsm
	A	B
1	List	City
2	ABC	Toronto
3	DEF	Toronto
4	GHI	Montreal
5	ABC	Montreal
6	DEF	Montreal
7	GHI	Montreal
8	ABC	Toronto
9
10
11	List	City
12	ABC	Toronto
15	ABC	Montreal
18	ABC	Toronto
19
Dupes in Filter

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B12:B18	Cell Value	duplicates	text	NO

Btw I pressed Quote next to your comment, but it didn't show up in reply,

It depends exactly what you did but if you have clicked "Quote", especially multiple times, keep an eye out at the bottom of the Reply window for the 'Insert quotes...' button. Click that and you can reorder multiple quotes or deleted individual quotes and then put them all into your response at once.

.. but I still do not know your excel version(s).

 

[Akuini]

I don't think that it does in the way the OP wants. For example here I'm thinking the OP wants Toronto highlighted in the filtered list but not Montreal.

Ah, you're right, that's the problem.

@bigdan
Try this method:
This is an Event Procedure, you need to put it in the code module of the sheet in question (say sheet1). This is how:
Copy the code > open sheet1 > right click sheet1 tab > select View Code > paste the code.
Change the address: "A11", "B", "B11", "B12" (on the blue line) to suit.

How it works:
1. Double-clicking B11 will highlight the duplicate cells.
2. Double-clicking A11 will clear the highlight.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

On Error GoTo skip:

If Target.Cells.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Range("B11")) Is Nothing Then
        
        Application.EnableEvents = False
            Dim c As Range, x As Range, rg As Range, d As Object
                Set c = Range("B12", Cells(Rows.Count, "B").End(xlUp)).SpecialCells(xlCellTypeVisible)
                c.Interior.Color = xlNone
                Set d = CreateObject("scripting.dictionary")
                
                For Each x In c
                    If d.Exists(x.Value) Then
                        If Not rg Is Nothing Then
                            Set rg = Union(rg, Range(d(x.Value)), x)
                        Else
                            Set rg = Union(Range(d(x.Value)), x)
                        End If
                    Else
                        d(x.Value) = x.Address
                    End If
                Next
                
            
        If Not rg Is Nothing Then rg.Interior.Color = vbYellow
        Application.EnableEvents = True
        
    End If
    
    If Not Intersect(Target, Range("A11")) Is Nothing Then
            Range("B12", Cells(Rows.Count, "B").End(xlUp)).Interior.Color = xlNone
    End If
Exit Sub
skip:
Application.EnableEvents = True
MsgBox "Error number " & Err.Number & " : " & Err.Description
End Sub

bigdan - how-do-i-highlight-the-values-in-a-filtered-list - 1.xlsm
	A	B
1	List	City
2	ABC	Toronto
3	DEF	Toronto
4	GHI	Montreal
5	ABC	Montreal
6	DEF	Montreal
7	GHI	Montreal
8	ABC	Toronto
9
10
11	List	City
12	ABC	Toronto
15	ABC	Montreal
18	ABC	Toronto
Sheet2

 

[bigdan]

I don't think that it does in the way the OP wants. For example here I'm thinking the OP wants Toronto highlighted in the filtered list but not Montreal.

Yes exactly!

.. but I still do not know your excel version(s).

I have two computers. Most questions are on my work computer. I just went to the About section, it says MS Office Standard 2013. So is the answer Excel 2013?

@bigdan
Try this method:
This is an Event Procedure, you need to put it in the code module of the sheet in question (say sheet1). This is how:
Copy the code > open sheet1 > right click sheet1 tab > select View Code > paste the code.
Change the address: "A11", "B", "B11", "B12" (on the blue line) to suit.

Thanks! This seems a bit complicated and I dont know VBA very well so I'll take a bit to add this. Will give it a shot.
I'm a bit confused about the cell references though. What would I replace those references with? The filtered list will keep changing, I assume I dont have to update the cell references each time?

 

[Peter_SSs]

I have two computers. Most questions are on my work computer. I just went to the About section, it says MS Office Standard 2013. So is the answer Excel 2013?

You can indicate more than one version in your forum profile if you want.

 

[Akuini]

I'm a bit confused about the cell references though. What would I replace those references with?

B11 is the header where you want to highlight the duplicate values.
A11 actually can be any header, you need it to clear the highlight by double-click.

The filtered list will keep changing, I assume I don't have to update the cell references each time?

No, it will change automatically as your data change.

If you need help then you can post your actual data set up, so I can amend the code to suit.