Compare, Search and highlight

Vishaal

Well-known Member
Joined
Mar 16, 2019
Messages
530
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
Thanks in Advance,

we have the following sheet

Excel 2010 32 bit
A
B
C
1
2
Emma
3
Olivia
4
Charlotte
5
Harper
6
Scarlet
7
Charlotte
8
Ava
9
Mia
10
Scarlet
11
Ava
12
Charlotte
13
Olivia
14
Sheet: Sheet1

We want to following result

Excel 2010 32 bit
A
B
C
1
2
Emma
3
Harper, IsabellaOlivia
4
Charlotte
5
Harper
6
Isabella, MiaScarlet
7
Charlotte
8
Ava
9
Mia
10
Ava, AmeliaScarlet
11
Ava
12
Charlotte
13
Olivia
14
Sheet: Sheet1

in this result, we have checked

1. A3 value in B3 to last row
2. A6 value in B6 to last row
3. A10 value in B10 to last row
4. Any value (name or numerical) anywhere in column we put it start search in column B (same row) and highlight the cell

Pls provide any code or vba or formula
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi Vishaal,
Here is a code that I think does what you need
Code:
Sub highlight()
'
' highlight cells in B with same name as in A
'
Dim ra, rb, ca, cb As Range
Dim na() As String
Dim nm As Variant
Dim rowa, rowb As Integer
Set ra = Range("a1", Range("a50000").End(xlUp))
Set rb = Range("b1", Range("b50000").End(xlUp))
For Each ca In ra
    rowa = ca.Row()
    na() = Split(Replace(ca.Text, " ", ""), ",")
    For Each nm In na()
        For Each cb In rb
            rowb = cb.Row()
            If rowb >= rowa Then
                If cb.Text = nm Then
                    cb.Interior.ColorIndex = 6
                    Exit For
                End If
            End If
        Next cb
    Next nm
Next ca
End Sub

And here is a test sheet https://1drv.ms/x/s!AovCE1fDrrdSniNex-a2_YlacBtj

Cheers
Sergio
 
Last edited:
Upvote 0
You have attached the sheet with solution, its help me a lot, Thanks Sir,

Is this possible in this vba,

It will search all value but highlight only first find value not all

I mean if its searching Harper, Isabella and if its find first Isabella then highlight only Isabella, pls check following sheet

Excel 2010 32 bit
A
B
C
1
2
Emma
3
Harper, IsabellaOlivia
4
Charlotte
5
Isabella
6
Charlotte
7
Harper
8
Amelia, MiaScarlet
9
Charlotte
10
Ava
11
Amelia
12
Scarlet
13
Mia
14
Olivia, ScarletHarper
15
Scarlet
16
Charlotte
17
Olivia
18
Sheet: Sheet1
 
Last edited:
Upvote 0
Hi Vishaal,
I think it is possible, I have to use a row flag for column A
Where are you from Vishaal, what country?
Cheers
Sergio
 
Upvote 0
India
City : Agra (The Taj Mahal City)

Your welcome in Agra
 
Upvote 0
Hi Vishaal,
Here is the code that does what you need with the first found name in the tuple criteria

Code:
Sub highlight()
'
' highlight cells in B with same name as in A
'
Dim ra, rb, ca, cb As Range
Dim na() As String
Dim nm As Variant
Dim rowa, rowb, hlrow As Integer
Set ra = Range("a1", Range("a32000").End(xlUp))
Set rb = Range("b1", Range("b32000").End(xlUp))
For Each ca In ra
    rowa = ca.Row()
    na() = Split(Replace(ca.Text, " ", ""), ",")
    hlrow = 32000
    For Each nm In na()
        For Each cb In rb
            rowb = cb.Row()
            If rowb >= rowa Then
                If cb.Text = nm Then
                    If rowb < hlrow Then
                        cb.Interior.ColorIndex = 6
                        If hlrow < 32000 Then
                            Range("B" & hlrow).Interior.ColorIndex = xlAutomatic
                        End If
                        hlrow = rowb
                    End If
                    Exit For
                End If
            End If
        Next cb
    Next nm
Next ca
End Sub

Cheers
Sergio
 
Upvote 0
Thanks for the update

I have checked and find that its working only for first entry in coloumn a

When we have entered second value its not searching

Help pls
 
Last edited:
Upvote 0
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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