How to look up for the same value in different worksheets through VBA?

[sm3117]

Hi,

I need to look up for a range of values in all the available worksheets, and in case there is any matches, then colour the value on the original range.

I have tried the following code:

Sub Compare3()


Dim WorkRng1 As Range
Dim WorkRng2 As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim DataRange As Range
Dim ws As Worksheet


xTitleId = "Buscar coincidencias"


Set WorkRng1 = Application.InputBox("Seleccionar equipos con cambios:", xTitleId, "", Type:=8)
Set WorkRng2 = Range("B1" & LastRow)


For Each Rng1 In WorkRng1
rng1Value = Rng1.Value
For Each ws In ActiveWorkbook.Worksheets
For Each Rng2 In WorkRng2
If rng1Value = Rng2.Value Then
Rng1.Interior.Color = VBA.RGB(200, 250, 200)
Exit For
End If
Next
Next
Next


End Sub

But it does not make any changes when there are matches.... Could someone help??
Many thanks,
Have a good day

 

[Meesam_ali]

How are you trying to use the code? I did a trial check and when I select for example few cells on "Sheet1" and if there is a match in some other sheet in "B1" column I do get the cells highlighted on "Sheet1".

 

[NotoriousPopol]

Hi,

is the data in the other worksheets always in the column B?

In your code, LastRow means nothing. If I understand what you're trying to do, you should have something like :

For Each ws In ActiveWorkbook.Worksheets
    LastRow=ws.Range("B1000").End(xlUp).Row
    Set WorkRng2 = ws.Range(Cells(1,2), Cells(LastRow,2))

 

[sm3117]

How are you trying to use the code? I did a trial check and when I select for example few cells on "Sheet1" and if there is a match in some other sheet in "B1" column I do get the cells highlighted on "Sheet1".

Really? That's the result I'm looking for, but I don´t really achieve it..I get no colour in any cell. I have also tried in a sample file, but no result neither

 

[sm3117]

Hi,

Thank you for answering,

Do you mean the following code?- It does neither work for me, it breaks in the highlighted line.

Sub Compare3()


Dim WorkRng1 As Range
Dim WorkRng2 As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim DataRange As Range
Dim ws As Worksheet


xTitleId = "Buscar coincidencias"


Set WorkRng1 = Application.InputBox("Seleccionar equipos con cambios:", xTitleId, "", Type:=8)


For Each Rng1 In WorkRng1
rng1Value = Rng1.Value
For Each ws In ActiveWorkbook.Worksheets
LastRow = ws.Range("B1000").End(xlUp).Row
Set WorkRng2 = ws.Range(Cells(1, 2), Cells(LastRow, 2))

For Each Rng2 In WorkRng2
If rng1Value = Rng2.Value Then
Rng1.Interior.Color = VBA.RGB(200, 250, 200)
Exit For
End If
Next
Next
Next




End Sub

May you give further help?
Thanks,

 

[sm3117]

Hi,

is the data in the other worksheets always in the column B?

In your code, LastRow means nothing. If I understand what you're trying to do, you should have something like :

For Each ws In ActiveWorkbook.Worksheets
    LastRow=ws.Range("B1000").End(xlUp).Row
    Set WorkRng2 = ws.Range(Cells(1,2), Cells(LastRow,2))

Hi,

Thank you for answering,

Do you mean the following code?- It does neither work for me, it breaks in the highlighted line.

[COLOR=#333333]Sub Compare3()[/COLOR]


[COLOR=#333333]Dim WorkRng1 As Range[/COLOR]
[COLOR=#333333]Dim WorkRng2 As Range[/COLOR]
[COLOR=#333333]Dim Rng1 As Range[/COLOR]
[COLOR=#333333]Dim Rng2 As Range[/COLOR]
[COLOR=#333333]Dim DataRange As Range[/COLOR]
[COLOR=#333333]Dim ws As Worksheet[/COLOR]


[COLOR=#333333]xTitleId = "Buscar coincidencias"[/COLOR]


[COLOR=#333333]Set WorkRng1 = Application.InputBox("Seleccionar equipos con cambios:", xTitleId, "", Type:=8)[/COLOR]


[COLOR=#333333]For Each Rng1 In WorkRng1[/COLOR]
[COLOR=#333333]rng1Value = Rng1.Value[/COLOR]
[COLOR=#333333]For Each ws In ActiveWorkbook.Worksheets[/COLOR]
[COLOR=#333333]LastRow = ws.Range("B1000").End(xlUp).Row[/COLOR]
[COLOR=#a52a2a][B]Set WorkRng2 = ws.Range(Cells(1, 2), Cells(LastRow, 2))[/B][/COLOR]

[COLOR=#333333]For Each Rng2 In WorkRng2[/COLOR]
[COLOR=#333333]If rng1Value = Rng2.Value Then[/COLOR]
[COLOR=#333333]Rng1.Interior.Color = [/COLOR]<acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>[COLOR=#333333].RGB(200, 250, 200)[/COLOR]
[COLOR=#333333]Exit For[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]Next[/COLOR]
[COLOR=#333333]Next[/COLOR]
[COLOR=#333333]Next[/COLOR]




[COLOR=#333333]End Sub[/COLOR][CODE]

[COLOR=#333333]May you give further help?[/COLOR]
[COLOR=#333333]Thanks,[/COLOR]

 

[NotoriousPopol]

Try with

[COLOR=#a52a2a][B]Set WorkRng2 = ws.Range(ws.Cells(1, 2), ws.Cells(LastRow, 2))[/B][/COLOR]

 

[sm3117]

Try with

[COLOR=#a52a2a][B]Set WorkRng2 = ws.Range(ws.Cells(1, 2), ws.Cells(LastRow, 2))[/B][/COLOR]

It is working now, thank you very much!

The issue I am trying to solve now is that it just colours the cells with the SAME value but it does not identify those with the contained value, I mean:

If in the original range I have: "AD456", and in any "B" column throughout the worksheets I have: "AD456/A", it does not colour the orginial cell.

Any help regarding this?

 

[NotoriousPopol]

Is this always like this? The original text is on the left and you could have something after (on the right)?

If so, replace the if statement :

[COLOR=#333333]If Rng2.Value Like rng1Value & "*" Then[/COLOR]

 

[sm3117]

Is this always like this? The original text is on the left and you could have something after (on the right)?

If so, replace the if statement :

[COLOR=#333333]If Rng2.Value Like rng1Value & "*" Then[/COLOR]

Yes, it is on the left, but just in case in the future there could be something on the right I have tried with the following code, and it also worked:

If InStr(Rng2.Value, rng1value) Then

Thanks a lot for your help, it has been really useful!