VBA - Highlighting a cell if it contains red text-CodePudding

I'm trying to write/run a VBA macro that highlights all cells in a workbook that contain red font (although, ALL characters within a string in given cell may not be red). The macro I've come up with falls short in that it only highlights cells that contain ONLY red font. I want it to highlight cells that may contain black text as well as red text. Here's the macro I've come up with: '''

Sub HighlightCell()
Set ws = Sheets("MySheet")
For r = 1 To 104
    For c = 1 To 36
        If (ws.Cells(r, c).Font.Color = 255) Then
            'set the desired color index
            ws.Cells(r, c).Interior.ColorIndex = 34
        End If
    Next c
Next r
End Sub

Is there a better macro to do this? Thank you so much.

CodePudding user response：

Like this:

Sub HighlightCell()
    Dim ws As Worksheet, c As Range, i As Long

    For Each ws in ActiveWorkbook.Worksheets  'or ThisWorkbook.Worksheets
        For Each c In ws.Range("A1").Resize(104, 36).Cells
            If Len(c.Value) > 0 Then                  'if cell has any text
                
                If c.Font.Color = 255 Then            'all text is red ?
                    c.Interior.ColorIndex = 34
                ElseIf IsNull(c.Font.Color) Then      'mixed font color?
                    For i = 1 To Len(c.Value)
                        If c.Characters(i, 1).Font.Color = 255 Then
                            c.Interior.ColorIndex = 34
                            Exit For                  'no need to check further
                        End If
                    Next i
                End If
    
            End If 'has any text
        Next c
    Next ws

End Sub