VBA Wildcard

[hsbobis]

I have two columns from 2 worksheets. In the first worksheet a column contains a list of project names that is active. The second worksheet contains just a list of ptoject names. I need to compare the project names from the first worksheet and highlight a cell if its value does match with wildcard value in the second sheet. I have this code that compare and hightlight the exact value but what I need is to compare it using wildcard. Thanks in advance.

Sub CompareAndHighlight()
    Dim rng1 As Range, rng2 As Range, i As Integer, j As Integer
    Dim isMatch As Boolean
' sheet/values to be highlighted
    For i = 2 To Sheets("Sheet").Range("D" & Rows.Count).End(xlUp).Row
        isMatch = False
        Set rng1 = Sheets("Sheet1").Range("D" & i)
' lookup sheet
        For j = 1 To Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
            Set rng2 = Sheets("Sheet2").Range("A" & j)
            If StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) = 0 Then
                isMatch = True
                Exit For
            End If
            Set rng2 = Nothing
        Next j
' highlight color
        If isMatch Then
            rng1.Interior.Color = RGB(128, 128, 128)
           
        End If
        Set rng1 = Nothing
    Next i
End Sub

 

[mrmmickle1]

You would typically use something like this:

[COLOR=#0000ff]Sub[/COLOR] Test()
 
    String1 = Range("A1").Text
    String2 = Range("A2").Text
    
   [COLOR=#0000ff] If[/COLOR] String1 Like "*" & String2 & "*" [COLOR=#0000ff]Then[/COLOR]
         Range("A2").Interior.Color = RGB(128, 128, 128)
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

Just curious what type of values are you comparing? What are a few examples?

 

[hsbobis]

You would typically use something like this:

[COLOR=#0000ff]Sub[/COLOR] Test()
 
    String1 = Range("A1").Text
    String2 = Range("A2").Text
    
   [COLOR=#0000ff] If[/COLOR] String1 Like "*" & String2 & "*" [COLOR=#0000ff]Then[/COLOR]
         Range("A2").Interior.Color = RGB(128, 128, 128)
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

Just curious what type of values are you comparing? What are a few examples?

Comparing project bids. The problem is the project names send to us are not exact match as project names on our database, so I have to set a specific keyword to look for that project name. That's why I think wildcard is perfect for that to display all projects with the specific keyword.

Can you please help me insert wild card on my code? i can't figure it out. thanks

 

[mrmmickle1]

This is the wildcard portion (the asterisks):

    String1 = Range("A1").Text
    String2 = Range("A2").Text
    
   [COLOR=#0000ff] If [/COLOR]String1 [COLOR=#ff0000]Like "*" & String2 & "*"[/COLOR] [COLOR=#0000ff]Then[/COLOR]
           isMatch = [COLOR=#0000ff]True[/COLOR]
[COLOR=#0000ff]        Exit For[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]