format text in cell

[pwpd406]

I am trying to enter distances in Excel cells and want the feet to be
normal script and the inches be in underlined superscript. Is there a
VBA code that will automatically convert the last two digits entered
into the cell? Any help will be appreciated.

 

[Tom Ogilvy]

Sub AAA()
With ActiveCell.Characters(Len(ActiveCell.Value) - 1, 2).Font
.Superscript = True
.Underline = True
End With

End Sub

possibly. This only works with constant values - not cells containing
formulas.

 

[L Scholes]

How do I get this only in selected cells? I have two columns (separated
by several rows) so I have 4 printable sheets. I only want the
"Measurement" cells to format this way, not the other, "Description,"
cells.

 

[Tom Ogilvy]

Sub AAA()
for each cell in Range("A2:A20,C2:C20,D2:E20")
With Cell.Characters(Len(Cell.Value) - 1, 2).Font
.Superscript = True
.Underline = True
End With
Next
End Sub

Change the range reference to match the layout of your data.

 

[L Scholes]

I used this code in both a module and the code for Sheet1 and it
doesn't seem to have any effect.??? (When I get this sheet working I
will change the references as appropriate.)

Sub AAA()
For Each Cell In Range("C5:C59,D559")
With Cell.Characters(Len(Cell.Value) - 1, 2).Font
.Superscript = True
.Underline = True
End With
Next
End Sub

 

[Tom Ogilvy]

This is called rich text formatting and can only be done on cells that
contain TEXT Constants (not numbers, not formulas). If your cells contain
numbers, then you would modify it to make them text as I have done below


Sub AAA()
For Each Cell In Range("C5:C59,D559")
cell.Value = "'" & cell.Text
With Cell.Characters(Len(Cell.Value) - 1, 2).Font
.Superscript = True
.Underline = True
End With
Next
End Sub

 

[L Scholes]

I really appreciate your help, but none of this working. I made the
indicated changes on both Sheet1 and module. Am I putting the code in
the wrong place?

 

[Tom Ogilvy]

You have to run the code.

 

[L Scholes]

I used this code in both a module and the code for Sheet1 and it
doesn't seem to have any effect.??? (When I get this sheet working I
will change the references as appropriate.)

Sub AAA()
For Each Cell In Range("C5:C59,D559")
With Cell.Characters(Len(Cell.Value) - 1, 2).Font
.Superscript = True
.Underline = True
End With
Next
End Sub

 

[Tom Ogilvy]

What do you have stored in the cells?

 

[L Scholes]

Right now there is nothing in the cells. I keep the book as a template,
then add the measurements and save the book named as a separate case
number. I can delete all macros and send you a code if that helps.

 

[Tom Ogilvy]

Obviously if the cell is empty, the macro will do nothing.

I already know the macro works within the constraints I cited.

If you are expecting the macro to apply a format to the cell so if you
entered something it would appear as you describe, unfortunately, Excel does
not support that. The formatting is applied directly to a text string in
the cell. An empty cell remains an empty cell.

 

[L Scholes]

The cells are empty when I start, but I add the measurements, feet and
inches, as the information becomes available. Now I have to insert the
numbers, then select the inches portion (last two digits), right click,
select "Format Cells">"Font," then select "superscript," and
"underline." There is no way to do this automatically using a macro?
Some of these measurements run into the thousands.