Questions based on the need to find all data in a worksheet that matches the search key, and then doing something often arise. Here is the basic code structure.
Code:
'=====================================================
'- FIND & HIGHLIGHT CELLS DEPENDING ON CONTENT
'- Brian Baulsom January 2005
'=====================================================
Sub FindHiLight()
Dim MyFind As Variant
Dim MyNewValue As Variant
Dim FoundCell As Object
Dim Counter As Long
'-------------------------------------------------
'- SET SEARCH KEY
MyFind = InputBox("Please insert value to find.")
If MyFind = "" Then End
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 16
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address <> FirstAddress
End If
rsp = MsgBox("Found " & Counter)
End Sub
