The task here is rather simple: Make all the numbers in all the cells in the selected range go to subscript, and all the others to lose the subscript. The code works brilliantly when the original text has no subscripts or if all the characters in the cells are in subscript. When some of the characters are in subscript, it is only possible to set some more characters to .Subscript=True, but not to make some .Subscript=False - i.e. to remove them from subscript.
Here are the outcomes I get without the workaround:
AB2C -> AB2C
AB2C -> AB2C
AB2C -> AB2C
AB2C -> AB2C
I managed to work around this by adding two lines that first set everything to subscript and then remove subscript from everything - this works in this case, but in other cases I will need some of the formatting to be preserved while I switch the formatting of some other target substrings. I can always store the existing formatting in an temporary array or something like this, but somehow it seems to me strange that there isn't a more simple solution to this...
Any ideas why I cannot do cell.Font.Substring = False on such partially formatted cells? And how could I go around this in an elegant manner?
Here are the outcomes I get without the workaround:
AB2C -> AB2C
AB2C -> AB2C
AB2C -> AB2C
AB2C -> AB2C
I managed to work around this by adding two lines that first set everything to subscript and then remove subscript from everything - this works in this case, but in other cases I will need some of the formatting to be preserved while I switch the formatting of some other target substrings. I can always store the existing formatting in an temporary array or something like this, but somehow it seems to me strange that there isn't a more simple solution to this...
Any ideas why I cannot do cell.Font.Substring = False on such partially formatted cells? And how could I go around this in an elegant manner?
Code:
Sub DoTheFormat()
Dim List As Range, cell As Range
Dim Formula As String
Dim i As Integer
Set List = Application.Selection
Dim tmp As String
'**************************for debugging
List.Font.Subscript = True
List.Font.Subscript = False
'**************************/for debugging
For Each cell In List
For i = 1 To Len(cell)
tmp = Mid(cell, i, 1)
If tmp Like "#" Then
cell.Characters(i, 1).Font.Subscript = True
Else
cell.Characters(i, 1).Font.Subscript = False
End If
Next i
Next cell
End Sub