Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Styles in Excel > VBA Examples
Deze pagina in het Nederlands

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 56 in total (Show All Comments):

 


Comment by: Gunita (3/14/2017 4:05:04 PM)

Hi,
I would be great full if I could have some help on the code below.
When I select an item from the drop down list my code goes to palette sheet (Contains of 100 Product names. Cell “B5” has match formula and named as “Ref_rowOffset”. Next to Products, I have 4 colours for (Header, Row, Fill, Total) VBA code find’s selected drop down product in palette sheet and picks the colours and colours the table. Table has been referenced as (Header, Row, Fill, Total). But as I have some sheets that contains a pivot tables and slicers so the code won’t work for them. The idea is to name Styles box as let’s say as (Header, Row, Fill, Total) and use them.


Public Function get_color(str_type As String) As String
Dim iColOffset As Integer '
Dim strRange As Range 'Palette colour
Select Case str_type 'Client_Name
    Case Is = "Row"
        iColOffset = 1
    Case Is = "Header"
        iColOffset = 2
    Case Is = "Fill"
        iColOffset = 3
        Case Is = "Total"
        iColOffset = 4
    Case Else
End Select
iRowOffset = Sheets("Palette").Range("Ref_rowOffset").Value
Set strRange = Sheets("Palette").Range("B5")
get_color = strRange.Offset(iRowOffset, iColOffset).Interior.Color
'get_color = strRange.Interior.Color
End Function


Sub style()

Range("D6").Select
    With ActiveWorkbook.Styles("HEADER")
        .IncludeNumber = True
        .IncludeFont = True
        .IncludeAlignment = True
        .IncludeBorder = True
        .IncludePatterns = True
        .IncludeProtection = True
    End With
    With ActiveWorkbook.Styles("HEADER").Interior
        .Pattern = xlSolid
        .PatternColorIndex = 0
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = -0.249946592608417
        .PatternTintAndShade = 0
    End With
End Sub


Thank you for your help!

 


Comment by: Jan Karel Pieterse (3/14/2017 4:24:50 PM)

Hi Gunita,

Not sure I follow all of it, but couldn't you use conditional formatting to do this perhaps?

 


Comment by: Kevin W. (7/13/2017 5:02:51 AM)

In Rudra's comment above

(http://www.jkp-ads.com/Articles/styles06.asp?AllComments=True#20775)

How should that output differ from the code in your example "Creating a list of styles"?

I was hoping that code was in response to Doug's question: Is it possible to modify the script so that it only lists styles that have been used (applied) and not all styles in the workbook? But I get basically the same output from both.

You posted an answer to Doug, but just saying that it would be slow. Is there a way to do it with VBA, even if it takes a while to run, or did you just mean that it would have to be done manually, and therefore it would be slow?

Thanks for the code!
Kevin

 


Comment by: Jan Karel Pieterse (7/13/2017 11:32:25 AM)

Hi Kevin,

The reason I stated it would be slow is that you would have to use a modified version of the FindAStyle routine (turn it into a function for instance) which is shown at the top of this page:

Function IsStyleInUse(sStyleName As String) As Boolean
     Dim oSh As Worksheet
     Dim oCell As Range
     For Each oSh In ThisWorkbook.Worksheets
         For Each oCell In oSh.UsedRange.Cells
             If oCell.Style =sStyleName Then
                 IsStyleInUse = True
                 Exit Function
             End If
         Next
     Next
End Sub


So for each style you have, you would have the function run through all the used cells in all the worksheets in the workbook, making this relatively slow.

 


Comment by: Alex Fang (5/19/2018 4:48:05 AM)

Hi,Jan,

Is there any way to create the new styles based on the selected cell's style. Now my code is stupid that I have to get the style of the cell and set the same for the new styles.
Is there any quicker way? In excel, if I select one cell and create the new style, the new style is automatically created based on the selected cell. I try to record the Macro in excel but seams not successful. Can you help me?

 


Comment by: Alex Fang (5/19/2018 5:08:58 AM)

Hi, Jan,

I solve my problem, thanks.

 


Comment by: Skumar (7/4/2018 11:01:33 AM)

My issue is that I want to use this PageOfPages on the first page and duplicate it on other pages using the LINES TO REPEAT FROM THE TOP in the page format, but when I do, the PageOfPages always show the result for the first page on all following pages. For exemple, if I have 6 pages, they will all show Page 1 of 6. I understand that excel is repaeting the content of the first rows, but isn’t there a way to have those rows repeating but change the PageOfPages according to the page it is repeated on?

Thanks in advance.

 


Comment by: Jan Karel Pieterse (7/4/2018 11:43:39 AM)

Hi Skumar,

I'm afraid you cannot do that.

 


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.

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

I give permission to process this data and display my name and my comment on this website accoring to our Privacy Policy.