change fonts in custom number format

VBA_Newbie

Active Member
Joined
Jan 7, 2005
Messages
258
Hi folks,

I have a table of figures. Some of these figures have special meaning so I give them a custom format so that it is clearly visible to whomever is reading the table. For instance, suppose I decide that any value over 100 should have the letter "A" beside it (not actually the case, but it'll work for this example). So if the value is 101, I want the cell to appear as 101A (I still want to use the value of the cell for calculation, thus I do not format this as text). I was wondering if it was possible to do the same thing but have the letter "A" be a smaller font, or ideally, a subscript. Any ideas?

Many thanks.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

VBA_Newbie

Active Member
Joined
Jan 7, 2005
Messages
258
Hi Greg,

Thanks for the reply. I am aware of conditional formatting, but for my purposes it won't work. The example I gave is a little misleading, whether I choose to have a letter in the cell as well as a number has nothing to do with the value of one cell...that was just part of the example. I actually have a macro which tests values against one another to verify whether there is a statistically significant difference. The mechanics of it are quite complex, so I won't get into it. But it indicates differences using letters which represent the column. For instance, it could test the value in C1, against D1, E1, F1, G1, etc. If C1 is significantly higher than D1, it will put a "D" beside the value in C1, and so on for each other column (using custom number format). It is very well possible that the C1 could end up looking like: 45DEFGHI. The macro works beautifully, and there is no other way to do it (it took me months to get it right in VBA). I was just hoping to make the cell "look" better. I figure if the letters were smaller, it wouldn't distract from the value and would be easier to read. My guess is that it's not possible to change the font size of just the letters in the custom number format...I guess I'll have to live with it.

Thanks again!
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Format>Cells>Number Tab

Select Custom. For Type, enter

[>100]General"A";General

lenze
 

VBA_Newbie

Active Member
Joined
Jan 7, 2005
Messages
258

ADVERTISEMENT

Hi Lenze,

Thanks for the reply, but that doesn't let me change the fontsize of the letter "A". I'm thinking it's probably not possible.
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
AFAIK there is no way to have the number formatting's font differ from the number's font. You would have to append the letters as text after the number, make the cell's contents text. Then yes, you can format the letters as superscripts or subscripts. I would be inclined to just insert a extra column for the comments/notes codes and superscript/subscript the boys thattaway.
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
Mike,

Have a look at this thread. I think you'd find it interesting. It discusses some ideas on setting custom formatting (by hand), setting it via code and summing cells that have had text added to them that was not formatting and so would otherwise elude use in formulae.

HTH
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,001
Messages
5,834,820
Members
430,324
Latest member
bosphoruskid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top