In Excel how could I change font colour according to content of a cell

Question

I have a list of playing cards with each card in its own cell e.g. 3h 2s Kd Ah Jc.... To help with visualisation I wanted to change the font colour of the hearts and diamonds to red. Of course manually would be an option, but very tedious

I have tried (unsuccessfully) to write a vba script to do a search and replace using Excel VBA find and replace as an example. However as I am NOT changing the text it just looped continually. I also could not make the search format specific.

Sub Main()
Dim c As Range
Dim redCell As String

With Worksheets(1).Range("A1:A52")
Application.FindFormat.Clear
Application.FindFormat.Font.Color = rgbBlack 'Automatic was a problem
    Set c = .Find("h", LookIn:=xlValues, Searchformat:=True)
    If Not c Is Nothing Then
        redCell = c.Address
        Do
            Range(redCell).Font.Color = -16776961
            Set c = .FindNext(c)
        Loop While Not c Is Nothing
    End If
End With
End Sub

I also tried to use conditional formatting but could not find a criteria that worked.

=OR(FIND("h",A1)=2,FIND("d",A1)=2) 

generates a #VALUE! error Any pointers would be gratefully received.

Answer 1

You can also define condtional formatting rules in VBA, e.g.,

    Sub CondFormat()
    Dim c
    With Range("A1:A52").FormatConditions
        .Delete
        For Each c In Array("d", "h")
            .Add Type:=xlTextString, String:=c, TextOperator:=xlContains
            .Item(.Count).SetFirstPriority
            .Item(1).Font.Color = -16776961
            .Item(1).StopIfTrue = False
        Next c
    End With
End Sub