VBA to Find and Change Format including partial text

[tm11excel]

I use this code to find instances where text in cells is red and then highlight those cells.
Note that I don't want to change the red text, just want to highlight the cell.

Application.FindFormat.Font.Color = 255
Application.ReplaceFormat.Interior.Color = 65535
Range("A:N").Replace What:="", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=True, _
ReplaceFormat:=True
Application.FindFormat.Clear
Application.ReplaceFormat.Clear

The problem is where random text within cells was selected and changed to red font. Is there a way that these cells can be highlighted as well?

Thank you in advance!

 

[Georgiboy]

Welcome to the forum,

Not sure how that can be edited to do what you need but the below may help:

Sub test()
    Dim rCell As Range
    Dim x As Long
 
    For Each rCell In ActiveSheet.UsedRange
        For x = 1 To Len(rCell)
            If rCell.Characters(x, 1).Font.Color = vbRed Then
                rCell.Interior.Color = vbYellow
                Exit For
            End If
        Next x
    Next rCell
End Sub

 

[rlv01]

Similar to @Georgiboy, but limiting the range further to your A:N definition. The downside of this code is that it will be relatively slow if the number of cells is large.

    Dim rng As Range
    Dim R As Range
    Dim I As Long

    With ActiveSheet
        Set rng = Application.Intersect(.UsedRange, .Range("A:N"))
    End With
    
    For Each R In rng
        For I = 1 To Len(R.Text)
            If R.Characters(I, 1).Font.Color = vbRed Then
                R.Interior.Color = vbYellow
                Exit For
            End If
        Next I
    Next R