Styles in Excel

VBA examples and tools

The little VBA routines shown below will greatly easy your work with styles. As an important side effect, these also show you how the style object works in VBA.

Find cells with a certain style

This routine find cells with a style containing "demo" in its name:

Sub FindaStyle()
    Dim oSh As Worksheet
    Dim oCell As Range
    For Each oSh In ThisWorkbook.Worksheets
        For Each oCell In oSh.UsedRange.Cells
            If oCell.Style Like "*demo*" Then
                Application.GoTo oCell
                Stop
            End If
        Next
    Next
End Sub

As soon as a cell is encountered with a style that matches that name filter, the code stops (Stop) and you can check out the cell in detail.

Creating a list of styles

This sub adds a table of your styles on a worksheet named "Config - Styles":

Sub ListStyles()
    Dim oSt As Style
    Dim oCell As Range
    Dim lCount As Long
    Dim oStylesh As Worksheet
    Set oStylesh = ThisWorkbook.Worksheets("Config - Styles")
    With oStylesh
        lCount = oStylesh.UsedRange.Rows.Count + 1
        For Each oSt In ThisWorkbook.Styles
            On Error Resume Next
            Set oCell = Nothing
            Set oCell = Intersect(oStylesh.UsedRange, oStylesh.Range("A:A")).Find(oSt.Name, _
                oStylesh.Range("A1"), xlValues, xlWhole, , , False)
            If oCell Is Nothing Then
            lCount = lCount + 1
            .Cells(lCount, 1).Style = oSt.Name
            .Cells(lCount, 1).Value = oSt.NameLocal
            .Cells(lCount, 2).Style = oSt.Name
            End If
        Next
    End With
End Sub

Clear all formatting of cells and re-apply their styles

The code below removes all formatting of all cells and subsequently re-applies their style to them.

Watch out: if you have not adhered to using styles strictly, you will lose all formatting in your file!!!

Sub ReApplyStyles()
'Resets styles of cells to their original style (resets all formatting done on top of ANY style)
    Dim oCell As Range
    Dim oSh As Worksheet
    If MsgBox("Proceed with care:" & vbNewLine & vbNewLine & _
              "This routine will erase all formatting done on top of the existing cell styles." & vbNewLine & _
              "Continue?", vbCritical + vbOKCancel + vbDefaultButton2, GSAPPNAME) = vbOK Then
        For Each oSh In ActiveWindow.SelectedSheets
            For Each oCell In oSh.UsedRange.Cells
                If oCell.MergeArea.Cells.Count = 1 Then
                    oCell.Style = CStr(oCell.Style)
                End If
            Next
        Next
    End If
End Sub

Replace one style with another

The code below uses a list with two columns. The column on the left contains the names of existing styles. The column to its immediate right contains the names of the style you want to replace them with.

The code will run through the selected cells in the left column and check if the style name in the column to its right differs. If so, it will prompt you with the alternative name. Clicking OK will cause the code to update ALL cells to which the old style was applied to the new style. Before running this sub you need to select the cells in the left hand column.

Sub FixStyles()
'-------------------------------------------------------------------------
' Procedure : FixStyles
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse
' Created   : 4-10-2007
' Purpose   : Replaces styles with the replacement style as defined by a two column list.
'             column 1 should contain the existing style, col 2 the replacing style
'-------------------------------------------------------------------------
    Dim sOldSt As String
    Dim sNewSt As String
    Dim oSh As Worksheet
    Dim oCell As Range
    Dim oSourceCell As Range
    Set oSourceCell = ActiveCell
    While oSourceCell.Value <> ""
        sOldSt = oSourceCell.Value
        sNewSt = InputBox("Please enter replacement style for:" & sOldSt, "Style changer", oSourceCell.Offset(, 1).Value)
        If sNewSt = "" Then Exit Sub
        If sNewSt <> "" And sNewSt <> sOldSt Then
            For Each oSh In ThisWorkbook.Worksheets
                For Each oCell In oSh.UsedRange
                    If oCell.Style = sOldSt Then
                        Application.GoTo oCell
                        On Error Resume Next
                        oCell.Style = sNewSt
                    End If
                Next
            Next
        End If
        Set oSourceCell = oSourceCell.Offset(1)
    Wend
End Sub

Removing formating from an Excel Table

Suppose you have just converted a range to a table (see this article), but the range had some formatting set up such as background fills and borders. Tables allow you to format things like that automatically, but now your preexisting formatting messes up the table formatting. One way to overcome this is by changing the style of the cells in the table back to the Normal style. This however removes your number formats too. The little macro below fixes that by first making a copy of the normal style, setting its Number checkbox to false and then applying the new style without number format to the table. Finally it applies the tablestyle and deletes the temporary style:

Sub RemoveFormattingOfTable()
    Dim oStNormalNoNum As Style
    On Error Resume Next
    Set oStNormalNoNum = ActiveWorkbook.Styles("NormalNoNum")
    On Error GoTo 0
    If oStNormalNoNum Is Nothing Then
        ActiveWorkbook.Styles.Add "NormalNoNum"
        Set oStNormalNoNum = ActiveWorkbook.Styles("NormalNoNum")
        oStNormalNoNum.IncludeNumber = False
    End If
    With ActiveSheet.ListObjects(1)
        .Range.Style = "NormalNoNum"
        'Now apply tablestyle:
        .TableStyle = "TableStyleLight1"
    End With
    ActiveWorkbook.Styles("NormalNoNum").Delete
End Sub

Comments

Showing last 8 comments of 67 in total (Show All Comments):

Comment by: Chris Greaves (2-5-2019 16:31:00) deeplink to this comment

Hi Jan Karel
Thanks for blnFindaStyle. I have "borrowed" a copy.
Too, I have posted a follow-up question on Eileen's Lounge.

Post=251061

No need to post this here; I just wanted you to know that your name was being taken, but not in Vain.

Cheers
Chris

Comment by: Jan Karel Pieterse (2-5-2019 17:16:00) deeplink to this comment

Hi Chris,

You're welcome!

Comment by: Thaddeus Lesnik (9-8-2019 16:11:00) deeplink to this comment

I’d love to have a user menu box with two drop down lists, each populated with the list of styles.
The purpose of having two drop down boxes would be to select one style from the list then in listbox 1 then choose a different style from the list to replace it with using listbox 2. This would be useful if, for example, I have a style which got duplicated when I copy a sheet and the style intent is the same, but two slightly different name exist.

Comment by: Jan Karel Pieterse (9-8-2019 17:34:00) deeplink to this comment

Hi Thaddeus,

You can use the routine called FixStyles to find out how to implement a part of what you need.

Comment by: Thaddeus Lesnik (12-8-2019 21:08:00) deeplink to this comment

For what it's worth, here's a clean summary of what the code became (can be placed completely in the Userform code or broken into modules and forms). Note to webmaster, I can share the modules and forms if that would be useful.

Sub UserForm_Initialize()
    Dim oSt As Style
    For Each oSt In ThisWorkbook.Styles
        lstbxSource.AddItem oSt.Name
        lstbxDest.AddItem oSt.Name
    Next oSt
End Sub

Sub cmdChange_Click() 'this is the userform button.
    Dim strOldSt As String
    Dim strNewSt As String
        strOldSt = frmStyles.lstbxSource.Text
        strNewSt = frmStyles.lstbxDest.Text
     Call FixStyles(strOldSt, strNewSt)
     'Report on the Status of the Completion of the Process
    MsgBox "Cell Style has been remapped!", vbInformation
End Sub

Sub FixStyles(strOldSt As String, strNewSt As String)
'-------------------------------------------------------------------------
' Procedure : FixStyles
' Purpose : Replaces styles with the replacement style as defined by listboxes in a userform.
'             Listbox 1 should contain the existing style, Listbox 2 the replacing style
'-------------------------------------------------------------------------
    Dim oWs As Worksheet
    Dim oCell As Range
     If strNewSt = "" Then Exit Sub
        If strNewSt <> "" And strNewSt <> strOldSt Then
        For Each oWs In ThisWorkbook.Worksheets
            For Each oCell In oWs.UsedRange
                If oCell.Style = strOldSt Then
                    Application.GoTo oCell
                    On Error Resume Next
                    oCell.Style = strNewSt
                End If
            Next
        Next
    End If
End Sub

Comment by: Jan Karel Pieterse (27-8-2019 10:41:00) deeplink to this comment

Hi Thaddeus,

Thanks!

Comment by: PiecevCake (28-12-2019 23:48:00) deeplink to this comment

Hi Thaddeus,
Beginner users like me plagued with styles would hugely appreciate instructions how to use your code! )I tried pasting it in a module, returned "error-object required, tried to past in a form nothing happened?
Many thanks!

Comment by: Jan Karel Pieterse (6-1-2020 11:46:00) deeplink to this comment

Hi PiecevCake,

I've published your comment, but please note that the site does not motify previous commenters about your comment. This means it is not likely Thaddeus will respond to your request.

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.