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.


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

Third party tools

Speed up your file

The best tool to optimise your Excel model!

What has changed?

PerfectXL Compare, for Spreadsheet Comparison
This Excel compare tool is fast, reliable, and easy to use. Compare two Excel files now and see for yourself!
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
            End If
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
    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
    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
        End If
        Set oSourceCell = oSourceCell.Offset(1)
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
End Sub




All comments about this page:

Comment by: Ananda Sim (5-2-2009 15:53:39)

Thanks for the article and sample VBA code. I love styles and they're not pushed enough in Excel like they are in Word.


Comment by: AlexJ (6-2-2009 06:24:31)

Are we going to see a "Style Manager" application from you in future, Jan Karel?


Comment by: Jan Karel Pieterse (6-2-2009 06:56:08)

Hi Alex,

I have given that some serious thought. But this is quite a task, so I wouldn't hold your breath :-)


Comment by: Bob Phillips (19-2-2009 17:20:30)

I have written a Style Manager addin to go with an app I am writing that has heavy style usage. I needed as the builtin styles dialog is rubbsih, I needed to see all the styles, delete them easily, rename them, adopt a style, get a preview of the style and so on.

One of the big problems with styles in VBA is the lack of methods exposed, copying a style is not simple, you have to add a style based upon a cell. So you identify a cell with that style, or set that set to a style, and then add the style based upon that cell. You cannot create a style based upn another style. And the only other option is merge a workbook styles colletion, all of them.

Copying a style across workbooks is not straightforward.


Comment by: Jan Karel Pieterse (20-2-2009 05:08:18)

Hi Bob,

Sounds good. I can't wait to see it published!


Comment by: Jörgen Möller (13-1-2010 06:09:52)

Here is a developed version of your "FindaStyle", I wanted to be able to have it selecting all cells with a certain style.

So this goes through the active workbook sheet by sheet and selects all cells with a defined style.

Sub StyleFindAll()
'Jörgen Möller Jan 13, 2010
    Dim oSh As Worksheet, oCell As Range, sStyle As String, oStyleCells As Range, i As Long
    sStyle = InputBox("What Style (or part of style name) to look for?", "Style Find", "Currency")
    If sStyle <> "" Then sStyle = "*" & sStyle & "*" Else Exit Sub
    For Each oSh In ActiveWorkbook.Worksheets
        i = 0
        For Each oCell In oSh.UsedRange.Cells
            If oCell.Style Like sStyle Then
                If i > 0 Then
                    Set oStyleCells = Application.Union(oStyleCells, oCell)
                    Set oStyleCells = oCell
                End If
                i = i + 1
            End If
        Next oCell
        If i > 0 Then
        End If
    Next oSh
End Sub

Feel free to put it on the website.


Comment by: Jan Karel Pieterse (13-1-2010 06:12:57)

Hi Jörgen,

Thanks for the code!


Comment by: Jamie (20-3-2010 08:53:48)

I want to count how many orange cells I have in a column, how can I do this?


Comment by: Jan Karel Pieterse (20-3-2010 11:47:34)

Hi Jamie,



Comment by: Cammy (8-4-2010 08:57:15)

I have the need to change the formatting for a specific word within a cell based on the 1st character of the word from VB.Net. How can I enumerate through the words in a cell and then apply a format (like bold or italics) to only that word?

I have the code to access the specific range and cell but from there I am stuck.

Any help is greatly appreciated.



Comment by: Jan Karel Pieterse (8-4-2010 11:51:06)

Hi Cammy,

The proper syntax would be:

    With Worksheets("Sheet1").Range("A1").Characters(1, 10).Font
        .Bold = True
        .Color = vbRed
    End With


Comment by: Cammy (9-4-2010 06:08:28)

Thanks, this is just what I needed.


Comment by: Laura Hughes (19-5-2010 06:26:08)

I am in the process of trying to find all Styles that are used in the Worksheets. I have tried the code listed above that has the line:

If oCell.Style Like sStyle then

This line fails at runtime. It seems the model has changed for Excel 2010. Has anyone tried similar logic in Excel 2010?

Thank you for your time.


Comment by: Jan Karel Pieterse (19-5-2010 07:14:16)

Hi Laura,

The code works on my Excel 2010.
What error do you get exactly?


Comment by: Laura Hughes (19-5-2010 07:28:59)

Hello Jan,

I get the following:
An unhandled exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll

Additional information: Operator 'Like' is not defined for type 'Style' and string "_Book11".

I must have something set wrong.


Comment by: Jan Karel Pieterse (19-5-2010 23:12:41)

Hi Laura,

You did not mention you were writing this in VB.NET. Does VB.NET know the Like operator? Try changing the line to:

If lCase(oCell.Style) = lCase(sStyle) Then

(not sure if VB.NET knows the LCase function either)


Comment by: flora (20-5-2010 02:16:06)

i have this huge excel work book that i use. it has well outlined formulars and columm formats. but since last week, something is so wrong... when i update my sheet (which i do on weekly basis) and save,when i re-open it, the colummm that i was working on, which is in different colour, all gets out of format!! the line goes, it changes its font, and all the work in % goes into so so so disppointment because i use this sheet for presentation.

kindly help!


Comment by: Jan Karel Pieterse (20-5-2010 04:36:08)

Hi Flora,

No promises, but you can try sending the file to me so I can take a look.


Comment by: Richard (3-10-2010 23:55:11)

I am trying to set the line object color and type in an excel worksheet.
The color works fine but I cannot change the type eg dashed or solid line. Codes I am using in the macro as per below.
Worksheets("Map").Shapes(28).Line.ForeColor.RGB = RGB(255, 0, 0)
Worksheets("Map").Shapes(28).Line.LineStyle = Excel.XlLineStyle.xlDashDotDot


Comment by: Jan Karel Pieterse (3-10-2010 23:58:54)

Hi Richard,

This appears to be the correct syntax:

    With ActiveSheet.Shapes(1)
        .Line.DashStyle = msoLineDashDot
        .Line.Style = msoLineSingle
    End With


Comment by: Siddhant Chothe (17-4-2011 23:39:54)

I have got a method that reads styles from a excel table and adds these to thisWorkbook.styles collection. I have also written another method to read the added styles and show them all in a message box.
Both the methods work fine. But there is an issue I am facing. When I run these methods when a workbook is open, then the styles do get added in the thisWorkbook.styles collection. But once I save, close and reopen the workbook, only one of them is left in styles collection and others are not saved at all.
Code for your reference:-
Code to add styles

' sub that accepts the 3 column range where styles are defined.
Public Sub AddStylesFromDefinition(rngStyleDef As Range)

    Dim ws As Worksheet
    Set ws = rngStyleDef.Parent
    Dim colUnprotectedSheets As Collection
    Set colUnprotectedSheets = New Collection
    Set colUnprotectedSheets = WorkSheetLevel.GetUnprotectedSheets
    If rngStyleDef.Columns.Count = 3 Then
        Dim rngRow As Range
        Dim sStyle As String
        Dim sty As Style
        For Each rngRow In rngStyleDef.Rows
            sStyle = CStr(rngRow.Cells(1, 1).value)
            On Error Resume Next
            On Error GoTo 0
            Set sty = ThisWorkbook.Styles.Add(Name:=sStyle, BasedOn:=rngRow.Cells(1, 2))
    End If

            WorkSheetLevel.ProtectAllSheets colUnprotectedSheets
End Sub

' sub attached to a button
Sub RefreshStyleList()
Dim rng As Range
Set rng = Range("BWStyle_Definition")
AddStylesFromDefinition rng
End Sub


Comment by: Jan Karel Pieterse (18-4-2011 02:43:20)

Hi Siddhant,

Excel 2007 and 2010 are "smart" in that any workbook styles which are not used anywhere in the file are not saved with the workbook.
So to ensure all your styles are saved, you need a worksheet that lists them all AND uses them all.


Comment by: Siddhant Chothe (18-4-2011 23:09:44)

Hey thanks Jan!! Will try it out.


Comment by: Siddhant Chothe (30-4-2011 01:14:14)

Hi Jan,
I want to know whether is it possible to serialise a style object? I can certainly write out code to read style properties. But, when I was reading format from a cell part by part, the unset properties like "diagonal borders" were also read from nowhere. Now, may be not from a cell's format, can I do the same from a style object? Once successfully read, I want to serialise to text string which can be communicated anywhere I want. For serialisation, I might use JSON or XML, let's see.


Comment by: Jan Karel Pieterse (1-5-2011 21:45:03)

Hi Siddhant,

Not sure what you mean by serialise? In any case, you'll have to do each of the Style object's properties one-by-one, listing everyone in your code. You can find a tool called ObjectLister on my download page which helps getting all properties of an object.


Comment by: Siddhant Chothe (6-5-2011 06:13:03)

By serialise I mean representing the entire object in form of string. Just like JSON. For example assume a style object as:-

oStyle.Font.Size=15 ' don't recall exact attribute
oStyle.Font.Face="Times New Roman" ' here again don't recall exact attribute.
But, the main point is that I want to be able to read such oStyle object at run-time and formulate something like:-

strStyle="{IncludeFont:True,Font{Size:15,Face:Times New Roman}}"
and so on.


Comment by: Jan Karel Pieterse (6-5-2011 07:26:11)

Hi Siddhant,

It can be done, but you'll have to have each property you want to serialize in a list somewhere.

You can write generic code by using the VBA CallByName function, which accepts the Object in question as an object pointer and can use a string argument (and the value in question) to set which property/method to set/get/let.


Comment by: Alfred Vachris (9-5-2011 06:56:08)

I have run across many workbooks with styles that can't be deleted. Have you explored a VBA process to remove them by editing styles.xml?


Comment by: Jan Karel Pieterse (9-5-2011 23:45:33)

Hi Alfred,

Not directly, but you might use the code offered here to open the styles xml inside an xlsx/xlsm file:


Comment by: XLGeek (16-5-2011 23:01:03)

Alfred, you are correct: if you are running into a situation where you can't delete custom styles via VBA or Excel OM then the only way to delete them is by altering the contents of styles.xml. Although it can be done VBA is not the best option for this. Here is a link to the thread on this subject that might help:


Comment by: Alfred Vachris (11-8-2011 05:44:43)

Jan Thank you very much for your code:
Using that as a base I have been able to build a process that rapidly reduces all cellStyles and cellStyleXfs collection to a single Normal style. I have expanded the process into a batch process can be applied to a collection of 2007 or 2010 workbooks.
Thanks Again


Comment by: Alfred Vachris (12-8-2011 07:03:11)

FYI - Workbooks saved as XLSB have .bin elements and not .xlm. So the neat tools here don't apply.


Comment by: Gabriel (11-2-2013 23:31:33)

This is a different topic. I have ~1000 word files containing hymns text and i need to create PPT for each file with the following:
Slide #1: contains text starting from the begining of the file until the macro finds double blank lines
Slide # 2: starts after the double blank lines until the macro finds the next double blank lines
and so on until the end of the text

Also I need the option of selecting:
1) slide background
2) Font type and size
3) Font color
4) PPT will be saved using the same name of the word file

Also if the macro finds the word "Chorus", then the slide for the chorus will be repeated after each verse.

Thank you so much.


Comment by: Jan Karel Pieterse (12-2-2013 08:52:36)

Hi Gabriel,

I suggest you to ask your question at there are lots of very smart people there who know Word VBA too.


Comment by: Tiaan Dreyer (15-3-2013 00:34:36)

My cell styles (options in the ribbon) have corrupted after importing a sheet into a workbook. I have tried to merge with a new book to get the custom styles back but it does not work. I know there is a VBA macro one can run to reset cell styles to the custom options, but I cannot seem to find it. Can you please help.


Comment by: Jan Karel Pieterse (15-3-2013 13:11:06)

Hi Tiaan,

This is one way:


Comment by: Doug (14-8-2013 22:43:19)

Hi -

A question regarding your 'creating a list of styles' sub: any reason why it shouldn't run in excel mac 2004?

I'm getting a syntax error when I try to run it and I'm stumped!


Comment by: Jan Karel Pieterse (15-8-2013 08:12:29)

Hi Doug,

On which line does the error occur?

I don't have a MAC, so this question is hard to answer!


Comment by: Doug (15-8-2013 21:11:09)

OK - Resolved the syntax error by pasting the script in Word Wrangler and resetting the indentation -- then pasting it through to VB (for some reason the VB editor didn't like the cut and paste directly from the web page?)

Now I'm getting a 'subscript out of range error' on this line:

    Set oStylesh = ThisWorkbook.Worksheets("Config - Styles")

Any idea why?


Comment by: Jan Karel Pieterse (16-8-2013 15:42:09)

Hi Doug,

apparently you do not have a worksheet names 'Config - Styles'


Comment by: Doug (2-2-2014 16:00:01)

I've been using the ListStyles script and find it quite useful. I had resolved my earlier syntax error by:

1. adding a line before the Set statement:
Worksheets.Add().Name = "Config - Styles"

2. switching
, (as the sub is in Personal.xls)

so I now have a one-click macro to list styles in any workbook.

My 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? (or should I ask the question on Eileen's lounge?)

Thanks for some great tools!


Comment by: Jan Karel Pieterse (2-2-2014 21:35:37)

Hi Doug,

Unfortunately, what it takes is run thrugh all cells in your file and get their style. Then check that list against the list of styles t find out the unused ones. Very slow.


Comment by: Rudra (18-4-2014 10:25:29)

To list Styles

Sub ListAllStyles()
Dim MyStyle As Style
Dim MyRow As Integer

MyRow = 1
For Each MyStyle In ThisWorkbook.Styles
     With Cells(MyRow, 1): .Value = MyStyle.Name: .Style = MyStyle.Name: End With
    MyRow = MyRow + 1
End Sub


Comment by: Andy Kingston (24-4-2014 09:53:47)


I have a worksheet formatted with a lot of cell styles, which I want to copy into another workbook, which also uses (different) cell styles.

I don't want the cell styles to be copied across to the workbook, but I would like the worksheet to retain its formatting (i.e. look the same but without styles)

Is there a way to deviate each cell on a sheet from it's style?... I can then remove all styles from the worksheet before copying into the workbook

NB: I've had some limited success with:

select all > increase font size and select all > italicize followed by select all > decrease font size and select all > de-italicize this seems to deviate the number formats and fonts etc, but the fill colours / outline still get scrapped if I then remove all cell styles.


Comment by: Jan Karel Pieterse (28-4-2014 10:14:31)

Hi Andy,

I don't really know. I would probably write some code that does a copy, paste special Values (or formulas, whichever is needed) and then loop through the cells to set their style names to match the source cells style names.


Comment by: Keith Howard (19-11-2014 21:49:19)

In my spreadsheet standards, I apply a style (i.e., not the Normal style) to every cell in the UsedRange. However, when I refer to the .Style property of a single cell range, if the range is empty, the code crashes, indicating that that value of .Style for that range is Nothing. That is strange, because when you look at such cell in Excel, at has a style attached to it.
Any ideas?
Many thanks.


Comment by: Jan Karel Pieterse (20-11-2014 10:32:54)

Hi Keith,

Can you share a bit of the code please?


Comment by: Keith Howard (22-11-2014 15:43:31)

Hello, Thanks for your response. I seem to have fixed the problem, although I am not really sure how. I think that the problem related to an error in how I was using offsets. Basically, I was not referencing the cell that I thought I was referencing.
Thanks and regards,


Comment by: Gunita (14-3-2017 16:05:04)

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()

    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 (14-3-2017 16:24:50)

Hi Gunita,

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


Comment by: Kevin W. (13-7-2017 05:02:51)

In Rudra's comment above


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!


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

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
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 (19-5-2018 04:48:05)


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 (19-5-2018 05:08:58)

Hi, Jan,

I solve my problem, thanks.


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

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 (4-7-2018 11:43:39)

Hi Skumar,

I'm afraid you cannot do that.


Comment by: Andy Smith (22-8-2018 21:44:51)

I have many files with 43,000+ styles in them. The styles are junk, they're unused, some have funny names that begin with a space and thereafter consist of digits and spaces, and some have funnier non-ASCII-character names.

I've written a bunch of code to delete them, unlocking them first, but there are about 2,000+ of the original 43,000+ that just won't go away, even though my code that deletes each one yields no error at all. I even have code to extract the styles.xml file from the xl folder of the zip and replace the entire <cellstyles> block, after which Excel wants to repair the file.

It seems all the ones that stay are the ones whose names begin with spaces and have only digits -- is that a clue?

Thanks in advance for your response


Comment by: Jan Karel Pieterse (23-8-2018 14:51:47)

Hi Andy,

Any odd characters in style names are indeed an indication there is something wrong with that style. Same goes for range names.


Comment by: Andy Smith (23-8-2018 19:15:07)

Re styles with funny names: I found that the ones that won't go away have names that begin with a space. How they got that way is unclear, but when I modified styles.xml in the xl folder, Excel first had to repair the file, but then they went away.


Comment by: Chris Greaves (2-5-2019 16:31:00)

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


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



Comment by: Jan Karel Pieterse (2-5-2019 17:16:00)

Hi Chris,

You're welcome!


Comment by: Thaddeus Lesnik (9-8-2019 16:11:00)

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)

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)

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
    End If
End Sub


Comment by: Jan Karel Pieterse (27-8-2019 10:41:00)

Hi Thaddeus,



Comment by: PiecevCake (28-12-2019 23:48:00)

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)

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:

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 (max 2000 characters):

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.