How to automatically identify contained values within text- VBA

[sm3117]

Hi,

I am working with the following code in order to identify if the selected values are present in "Column B" of other worksheets within the same file.

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

I would really appretiate further help!


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(ws.Cells(1,2),ws.Cells(LastRow,2))
For Each Rng2 In WorkRng2
If rng1Value = Rng2.Value Then
Rng1.Interior.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>.RGB(200, 250, 200)
Exit For
End If
Next
Next
Next

End Sub

Thank you,

Have a good day

 

[JLGWhiz]

Add the line shown in red font.

If rng1Value = Rng2.Value Then
Rng1.Interior.Color = VBA.RGB(200, 250, 200)
[COLOR=#B22222]Rng2.Interior.Color = VBA.RGB(200, 250, 200)[/COLOR]
Exit For
End If

 

[sm3117]

Add the line shown in red font.

If rng1Value = Rng2.Value Then
Rng1.Interior.Color = VBA.RGB(200, 250, 200)
[COLOR=#B22222]Rng2.Interior.Color = VBA.RGB(200, 250, 200)[/COLOR]
Exit For
End If

Thanks a lot for your answer.

Sorry if I have misexplained mi doubt- I wasn´t trying to highlight the "matched cell" but, trying to highlight the original one whenever there was the same value contained within the string.

For example, for a given product and its versions: AD456 & AD456A or AD456/2.

I have been able to solve it by changing the If statement with:

 If InStr(Rng2.Value, rng1value) Then