Find / Replace with Hilightling and Formatting?

  • Thread starter Thread starter Mr B
  • Start date Start date
M

Mr B

Howdy,

Is there any way to do a find and replace type thing except that when it finds a match, it will take
the cell and do something like highlight the cell (change background color) or make it bold or
something like that?

Thanks.
 
There's a pretty good example inside VBA's help for Find.

This was stolen mostly from there:

Option Explicit
Sub testme01()

Dim FoundCell As Range
Dim myRng As Range
Dim FirstAddress As String
Dim LookFor As String

If Selection.Cells.Count = 1 Then
Set myRng = ActiveSheet.UsedRange
Else
Set myRng = Selection
End If

LookFor = InputBox(prompt:="Look for what:")
If Trim(LookFor) = "" Then
Exit Sub
End If

With myRng
Set FoundCell = .Cells.Find(what:=LookFor, _
LookIn:=xlValues, lookat:=xlPart, _
searchdirection:=xlNext, _
searchorder:=xlByRows, MatchCase:=False)

If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
FoundCell.Interior.ColorIndex = 6
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address <> FirstAddress
End If
End With

End Sub

I made some assumptions about what to look for--part-not whole, too. But it
should get you started.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top