search & modify?

  • Thread starter Thread starter Stet
  • Start date Start date
S

Stet

I would like to search a very large excel file for a certain string and
modify it in each instance. However, I do not want to replace the
string. Instead, I need to modify the boldness of the string. Is
there a way to do this without a macro? If not, does anyone already
have a marcro which can be modified to do this? Thanks.

Stet
 
Here is a sub that may help. Modify to suit.

Sub Colorit()
For Each cel In [colorlist]
With Worksheets("TV").Cells
Set c = .Find(cel, LookAt:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 46
c.Font.ColorIndex = 2
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next cel
End Sub
 
Here's one that will find multiple instances of the input string
within a cell (and is case-insensitive):

Public Sub BoldCharacters()
Dim found As Range
Dim charStart As Integer
Dim lenResult As Integer
Dim firstAddr As String
Dim foundText As String
Dim result As String
result = Application.InputBox( _
Prompt:="Text to make bold: ", _
Title:="Search and Bold", _
Default:=ActiveCell.Text, _
Type:=2)
If result = "False" Or Len(result) = 0 Then Exit Sub
Set found = ActiveSheet.Cells.Find( _
what:=result, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If Not found Is Nothing Then
lenResult = Len(result)
firstAddr = found.Address
Do
foundText = UCase(found.Text)
charStart = InStr(1, foundText, UCase(result))
Do
found.Characters(charStart, _
lenResult).Font.Bold = True
charStart = InStr(charStart + 1, foundText, _
UCase(result))
Loop While charStart > 0
Set found = Cells.FindNext(after:=found)
Loop While found.Address <> firstAddr
End If
End Sub
 
Back
Top