Forum Discussion
Find text and replace with symbol
Hi, I have a cell with the text string "¤ Red" and want to replace the ¤ with a red dot. Using Find and Replace I can replace the ¤ with a dot but getting it red is a problem. If I use the "Copy for...
I don't think there is a built-in way to do this. Using a macro would be possible:
Sub ColorTheDots()
Dim oFirstFoundCell As Range
Dim oFound As Range
Dim lCt As Long
Dim sStr As String
Set oFirstFoundCell = ActiveSheet.UsedRange.Find(What:="¤", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
Set oFound = oFirstFoundCell
If Not oFirstFoundCell Is Nothing Then
Do
sStr = oFound.Value
For lCt = 1 To Len(sStr)
If Mid(sStr, lCt, 1) = "¤" Then
With oFound.Characters(Start:=lCt, Length:=1).Font
.Color = -16776961
End With
End If
Next
Set oFound = ActiveSheet.UsedRange.Find(What:="¤", _
After:=oFound, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
Loop Until oFirstFoundCell.Address = oFound.Address
End If
End Sub