If you are looking for partial match then change this:
[vba] 'Sheet 2 name is assumed as "Sheet2". Change it to suit
Set r = Sheets("Sheet2").Range("A:A").Find(c.Value, [A1], xlValues, xlWhole, _
xlByRows, xlNext, True)
[/vba] to:
[vba] 'Sheet 2 name is assumed as "Sheet2". Change it to suit
Set r = Sheets("Sheet2").Range("A1:A300").Find(c.Value, [A1], xlValues, xlPart, _
xlByRows, xlNext, True)
[/vba] You don't need to use wild card character then.

Edit: You can hard code the search range as shown above but that may not be good if you it is growing / dynamic.