VBA NumberFormat with Special Characters

[russellrichter]

I have a VBA module in which I need to add formatting to a number based on several criteria. (Excel 2003). If a record in the worksheet meets these criteria, the cell should be displayed as a right-facing triangle (U+25BA), the number and a left-facing triangle (U+25C4).

I am able to do this by copying the characters from the character map and pasting them into the custom number formatting dialog boxes in Excel. But, I can't seem to get that same effect in VBA using the Alt+[NumPad] method. Even when I record the custom formatting as a macro, the recorded macro does not set the format as it was recorded, but instead sets it to "? 0 ?".

So, is it possible in the Selection.NumberFormat command to set a format using these special characters?

Thanks for the help!

Russ
Houston

 

[RoryA]

Something like this should work:

activecell.NumberFormat = chrw(&H25BA) & "0" & chrw(&H25C4)

 

[lenze]

I have no idea if this will work, but it's worth a try. Create a Custom style (say myStyle) with this custom format. Now if your code, you can use something like

Range("$A$1").Style= "myStyle"

lenze

 

[russellrichter]

Thanks, Rory. That ChrW() function did the trick.

 

[Glasgowsmile]

Something like this should work:

activecell.NumberFormat = chrw(&H25BA) & "0" & chrw(&H25C4)

If it's okay, resurrecting this to see if I can find an answer to my own question. OP had left and right-facing arrows, what about up/down? Where would I find the codes for something like that? I've been searching the internet for probably 20 minutes and cannot find what I'm looking for. lol

For reference, this is what I'm trying to achieve in VBA

[Color10]$0.00▲;[Red]$0.00▼;

 

[Glasgowsmile]

Disregard, I used the OP unique in google and found this site, which gave me the answer I needed! - Find all Unicode Characters from Hieroglyphs to Dingbats – Unicode Compart