Formatting text

  • Thread starter Thread starter Keyrookie
  • Start date Start date
K

Keyrookie

Hey all,

I need to have a column of text represent key musical signatures, ie
Ab, Bb, C, F, etc. I'm trying to have the "b" be superscript. I nee
a condtional formula to change the lower case "b" to superscript.

Thanks for your help,

Keyrooki
 
Hi try this macro it also converts # to superscript so delete

Or sChr = "#"

if you do not want this.

Copy the code into the VB Editor (ALT + F11), Insert Modue, Paste the code
in module. Ctrl + Q to return to sheet.

Select the cells to format (I'd copy them first) and run the macro

ALT + F8, select macro and click Run.

Sub FormatFont()
Dim c
Dim sChr As String
Dim iLen As Integer
Dim i As Integer

'select the music
For Each c In Selection
'format each lower case b
For i = 1 To Len(c)
sChr = Mid(c, i, 1)
If sChr = "b" Or sChr = "#" Then
With ActiveCell.Characters(Start:=i, Length:=1).font
.Name = "Perpetua"
.FontStyle = "Italic"
.Superscript = True
End With
End If
Next i

Next
End Sub

Hope this helps.

Peter

Set the security level to Medium
 
whoops I only tested on one cell use this instead

Sub FormatFont()
Dim c
Dim sChr As String
Dim iLen As Integer
Dim i As Integer

'select the music
For Each c In Selection
c.Select
'format each lower case b and #
For i = 1 To Len(c)
sChr = Mid(c, i, 1)
If sChr = "b" Or sChr = "#" Then
With ActiveCell.Characters(Start:=i, Length:=1).font
.Name = "Perpetua"
.FontStyle = "Italic"
.Superscript = True
End With
End If
Next i

Next
End Sub

Just one extra line but..!

Peter
 
Back
Top