I want to put all the RED colored text in a cell , into an Array. Any help will be appreciated.
Below is situation -
My cell contain this text -> " This is testing to Red color. "
I want to populate an array Like , Array(0) = 'testing' and Array(1) = 'color'.
I am able to create a function which is scanning the entire cell which gives me 'testingcolor' . I am not able to identify the spaces. i will be good if this function can give me 'testing color'.Then i can scan for spaces and create the array. T
Function GetColorText(pRange As Range) As String
Dim xOut As String
Dim xValue As String
Dim i As Long
Dim j As Long
xValue = pRange.Text
j = VBA.Len(xValue)
For i = 1 To VBA.Len(xValue)
If pRange.Characters(i, 1).Font.Color = vbRed Then
xOut = xOut & VBA.Mid(xValue, i, 1)
End If
Next
GetColorText = xOut
End Function