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

bigdan

Well-known Member
Joined
Oct 5, 2009
Messages
840
Office Version
  1. 2013
Platform
  1. Windows
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.

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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
It works on filtered range:

Book1
A
1Z
2x
3R
4x
5
6List
7Z
8x
10x
16
17
18
19
20
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A6:A20Cell ValueduplicatestextNO
 
Upvote 0
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
AB
1ListCity
2ABCToronto
3DEFToronto
4GHIMontreal
5ABCMontreal
6DEFMontreal
7GHIMontreal
8ABCToronto
9
10
11ListCity
12ABCToronto
15ABCMontreal
18ABCToronto
19
Dupes in Filter
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B12:B18Cell ValueduplicatestextNO



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.
1629270014505.png



.. but I still do not know your excel version(s). ;)
 
Upvote 0
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.

Rich (BB code):
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
AB
1ListCity
2ABCToronto
3DEFToronto
4GHIMontreal
5ABCMontreal
6DEFMontreal
7GHIMontreal
8ABCToronto
9
10
11ListCity
12ABCToronto
15ABCMontreal
18ABCToronto
Sheet2
 
Upvote 0
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?
 
Upvote 0
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.
1629295383727.png
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

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