Sign in to follow this  
Followers 0
laffo16

ExcelCOM UDF - _ExcelFontGetColor (multiple font colours) question

8 posts in this topic

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.

Share this post


Link to post
Share on other sites



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.

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by laffo16

Share this post


Link to post
Share on other sites

bump

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by Spiff59

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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 by laffo16

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

thank you Spiff59, i'll checkout those leads.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0