Conditional Formatting in Excel using Alphanumerics

I have a conditional format in Excel that is displaying the up/down arrows based on certain values. This works just fine. However, in my spreadsheet (which is largely controlled by VBA) the user has the ability to review data in a 'grade' display (L=1,M=3,H=5) rather than the 1/3/5 score. I have a custom fun开发者_运维问答ction that can convert the L/M/H to a number, but I can't figure out how to incorporate this into the conditional format. I can do it in a normal conditional format, but I want the up/down arrows icon set. I'd remove the conditional formatting and apply an icon to the cell with VBA but I don't think that's possible. Any help?

You could either:

Mimic Conditional Formatting

Forget conditional formatting and use excel 2003 type methods with arrows and Wingdings font. Looks very similar, Andy Pope has a good example:Mimic 2007 Conditional Formatting Icon

Use an invisible helper column

The hidden helper column will display the numeric values 1,3,5 based on the actual cells L,M,H. Then in the cell with the L,M,H, you add the conditional and set it to the value of the helper column.

What I ended up doing was adding an additional cell to the right that used a UDF to convert the cell values to numbers. I then applied the conditional formatting to this new cell and set it to to display the icon only. By locking the icon cell and protecting the worksheet (was going to protect anyways) the field is never user editable which is exactly what I wanted.