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.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

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,014
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,014
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,383
Messages
5,571,818
Members
412,420
Latest member
Quintankerus
Top