Using a cell reference within a macro to find and replace

[Guest]

I currently use the following within a macro:

Range("B7:B100").Select
Application.ReplaceFormat.Interior.ColorIndex = 3
Selection.Replace What:="sutherland", Replacement:="Sutherland",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=True

I am not sure if this is the best way to accomplish what I need....but it
works. It looks for sutherland, finds it, and basicly shades the cell with
colorindex=3. Question is....Can I somehow substitute a cell reference in the
What:="xxx" area. I would like the macro to search the range for a match to a
specific cell and then replace with that cells value and color code the cell.

Thanks,

Ken

 

[Guest]

This should be close. Just change the cell references to A1...

Application.ReplaceFormat.Interior.ColorIndex = 3
Range("B7:B100").Replace What:=Range("A1").Value, _
Replacement:=Range("A1").Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=True