ClearFormats not Clearing All Formats

[Aaron.B]

Using Excel 2007. I have cell with several formats, and when I click "Clear Formats" (or use Range.ClearFormats), only the formats applied to first specially formatted character are removed:

<TABLE style="WIDTH: 66pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=88><COLGROUP><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 66pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=17 width=88>MyExample2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 66pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=17 width=88>Becomes:</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17>MyExample2</TD></TR></TBODY></TABLE>

As you can see, even though 2 is not underlined it stays bold.

If you have another version of Excel (97,2003,2010) is the behavior the same?

 

[Scott Huish]

Try this:

Sub test()
Range("G1").Style = "Normal"
End Sub

 

[Aaron.B]

Unfortunately "Style" is a property of the Range object and changes to Range's Style don't appear to cascade down to the Characters collection. So it's the exact same problem as ClearFormats.

This is long but it works:

Public Sub ClearCharFormats()
    Const xlThemeFontNone As Long = 0&
    Dim rngData As Excel.Range
    Dim rngCll As Excel.Range
    Dim fnt As Excel.Font
    Dim boolIs2007 As Boolean
    boolIs2007 = Val(Excel.Application.Version) >= 12#
    Set rngData = Intersect(Excel.ActiveSheet.UsedRange, Excel.Selection)
    rngData.ClearFormats
    If Not rngData Is Nothing Then
        For Each rngCll In rngData.Cells
            If LenB(rngCll.Formula) Then
                Set fnt = rngCll.Characters(1, Len(rngCll.Value)).Font
                With fnt
                    .Bold = False
                    .ColorIndex = xlAutomatic
                    .Italic = False
                    .name = Excel.Application.StandardFont
                    .Subscript = False
                    .Superscript = False
                    .Underline = False
                    .Size = Excel.Application.StandardFontSize
                    .Strikethrough = False
                    If boolIs2007 Then
                        'Call by name so code will compile in older versions.
                        CallByName fnt, "ThemeFont", VbLet, xlThemeFontNone
                        CallByName fnt, "TintAndShade", VbLet, 0&
                    End If
                End With
            End If
        Next
    End If
End Sub

I was just hoping for something via the interface or more concise.

 

[Scott Huish]

OK, how about this?

Sub Macro1()
On Error Resume Next
ActiveWorkbook.Styles.Add Name:="CleanFormats"
 With ActiveWorkbook.Styles("CleanFormats").Font
    .Bold = False
    .Italic = False
    .Underline = xlUnderlineStyleNone
    .Strikethrough = False
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
End With
Range("G1:G50").Style = "CleanFormats"
ActiveWorkbook.Styles("CleanFormats").Delete
End Sub

 

[Aaron.B]

Ahhh good way to go. That works. What an interesting discovery. After reading through your response I decided to try to edit the "Normal" style. Which you can do. Then I noticed something fun (which you might have already been on to)... "ClearFormats" isn't actually clearing the formats. It's just restoring the normal style. So if you make "Normal" Bold Purple text, it clear formats makes everything bold purple text.

The odd thing is that of all the styles "normal" is the only one that won't overwrite character formats. Not matter how you define "Normal".

So the solution using the interface is: Home Tab (Style Section)>Cell Styles>Right Click on Normal and select "Duplicate".

Then you can just format as "Normal 2" to strip formats.


Thanks HP that got me on the right path