How do I identify the starting position of red font? RRS feed

  • Question

  • Hello

    I am trying to find a way to identify the starting position of red font in a string of text in excel.  For instance, if I have "ABCDEFG" in a well, and the "B" is red, I would like to identify that the red "B" is at the second position.

    Any help would be appreciated!  Thank you!

    Wednesday, April 19, 2017 11:52 PM

All replies

  • Maybe something like this...

    Option Explicit
    Sub test()
        Dim rCell As Range
        Dim Pos As Long
        Set rCell = ActiveCell
        Pos = GetRedFontPosition(rCell)
        MsgBox Pos, vbInformation
    End Sub
    Function GetRedFontPosition(ByVal Target As Range) As Long
        Dim i As Long
        With Target
            For i = 1 To Len(.Value)
                If .Characters(i, 1).Font.Color = vbRed Then
                    GetRedFontPosition = i
                    Exit Function
                End If
            Next i
        End With
        GetRedFontPosition = 0
    End Function

    Note that you can also use the RGB function to define your color.  For red, as an example...

    If .Characters(i, 1).Font.Color = RGB(255, 0, 0) Then

    Hope this helps!

    Domenic Tamburino Microsoft MVP - Excel - "For Your Microsoft Excel Solutions"

    Thursday, April 20, 2017 12:46 AM