Option Explicit

Public Function Test(ByVal foo As Range) As Variant
    Dim pReg As Object, sXml As String, pItems As Object
    Dim vOut() As Variant, i As Long

    If foo.Count = 1 Then
        Set pReg = CreateObject("VBScript.RegExp")
        pReg.Global = True: pReg.IgnoreCase = True
        pReg.Pattern = "<Font[^>""]*?(?:html:Color=""(#[A-F\d]+)"")?>(.+?)</FONT>"
        sXml = foo.Value(xlRangeValueXMLSpreadsheet)
        Set pItems = pReg.Execute(sXml)
        ReDim vOut(0 To pItems.Count - 1, 1 To 2)
        For i = 0 To pItems.Count - 1
            vOut(i, 1) = pItems(i).SubMatches(1)
            vOut(i, 2) = pItems(i).SubMatches(0)
            If IsEmpty(vOut(i, 2)) Then vOut(i, 2) = "#000000"
        Next
        Test = vOut
    End If
End Function

Public Function OneColor(Cell As Range)
    OneColor = Cell.Font.Color
End Function