ExcelCOM UDF - _ExcelFontGetColor (multiple font colours) question

[laffo16]

in the data im processing, their are cells which look like

"*jimmy bob* **(12)**"

* = black

** = red

i need to check if the colour of the number in brackets is red.

_ExcelFontGetColor returns null for multiple font colours in same cell.

tried excelwrite into the same cell after a trimleft upto the brackets but it removes formating.

tried excelcopy(), edit the clip then excelpaste() but editing the clip removes formating.

im out of ideas, is their a way to edit the cell without overwriting the colour formating of specific txt or any other way of doing this. thanks yo!

ive also tried using =excelformula but also no format.

[laffo16]

also, whats the simplest way to strip a hex string of a value?, i have the hex value A0 which isnt being detected as a space and cant remove with stringstripws. i dont know much about hex but wouldnt a straight up stringreplace not work. do i need something like stringinstr and check the value exists, then is an odd number before triming.

[laffo16]

this is the hex char replace i wrote

Func _StringStripHexChar($SSHX_string, $SSHX_strip)
    $cell_name_hex = _StringToHex($SSHX_string)
    $occurrence = 0
    While 1
        $occurrence += 1
        $cell_name_loop = StringInStr($cell_name_hex, $SSHX_strip, 0, $occurrence)
        If $cell_name_loop = 0 Then
            ExitLoop
        EndIf
        If IsFloat($cell_name_loop / 2) Then
            $cell_name_hex = StringLeft($cell_name_hex, $cell_name_loop -1) & StringRight($cell_name_hex, StringLen($cell_name_hex) - $cell_name_loop -1)
            $occurrence += -1
        EndIf
    WEnd
    Return _HexToString($cell_name_hex)
EndFunc

still unable to resolve the font colour problem, although i now have a work around, would appreciate any comments regarding this.

Edited October 27, 2008 by laffo16

[laffo16]

bump

[Spiff59]

bump

Might I request a little clarification as to what you're wanting to do?

Are you just wanting to?

$newstring = StringStripWS(StringReplace($oldstring,Chr(160),Chr(32)),$stripmode)

Edited October 27, 2008 by Spiff59

[laffo16]

ahh crap, i could of probably done that instead, well nm thanks for the suggestion i havent taken chr() or asc() functions into my vocab yet. if anyone can help me with excel font issue that would be appreciated.

Edited October 27, 2008 by laffo16

[Spiff59]

I did the brute-force method and manually recorded a macro where I went and changed the color of a few characters in a cell.

It appears you're after "ActiveCell.Characters(Start:=1, Length:=2).Font.ColorIndex"

Googling 'Font.ColorIndex' got a few hits and it looks like you can loop through each character of a cell's value and either read or set the colorindex property.

[laffo16]

thank you Spiff59, i'll checkout those leads.