How do I copy only the red text in a cell to another cell?-CodePudding

I have cells that contain different colour text in Excel. I want to be able to extract the text that is in a particular colour to another cell.

How can I amend my UDF to account for this?

Function RedText(Rng As Range) As String
  Dim X As Long, S As String
  S = Rng.Text
  For X = 1 To Len(Rng.Text)
    If Rng.Characters(X, 1).Font.Color <> vbRed Then
      If Mid(S, X, 1) <> vbLf Then Mid(S, X, 1) = " "
    End If
  Next
  RedText = Replace(Replace(Application.Trim(S), " " & vbLf, vbLf), vbLf & " ", vbLf)
End Function

Thanks

CodePudding user response：

For example:

Function TextByColor(Rng As Range, hex As String) As String
  Dim X As Long, S As String, clr As Long
  S = Rng.Text
  clr = HexToRGB(hex)
  For X = 1 To Len(Rng.Text)
    If Rng.Characters(X, 1).Font.Color <> clr Then
      If Mid(S, X, 1) <> vbLf Then Mid(S, X, 1) = " "
    End If
  Next
  RedText = Replace(Replace(Application.Trim(S), " " & vbLf, vbLf), vbLf & " ", vbLf)
End Function

Function HexToRGB(hex As String) As Long
    Dim r, g, b
    b = Application.Hex2Dec(Right(hex, 2))
    g = Application.Hex2Dec(Mid(hex, 3, 2))
    r = Application.Hex2Dec(Left(hex, 2))
    Debug.Print r, g, b
    TextByColor = RGB(r, g, b)
End Function

Usage: =textbycolor(A3,"0000FF")