Styles in Excel



This article has also been published on Microsoft's MSDN site:

Using Styles to Dress Up Your Worksheets in Excel 2007

This article explains how you can use styles to ease maintenance of your spreadsheet models.

Microsoft has made it very easy to dress up your worksheets with all sorts of fill patterns, borders and other frills. Because formatting of cells is often done in an ad-hoc manner, many spreadsheet pages look messy.

By consistently using cell styles (instead of changing parts of the cell's formatting) you will be forced to think about the structure of your work. Religiously using styles may even force you to reconsider the overall structure of the entire spreadsheet model: The quality of the computational model itself may be positively affected.

I therefore consider Styles as being underused, underestimated and under exposed.

How styles work

A style is just a set of cell formatting settings which has been given a name. All cells to which a style has been applied look the same formatting-wise. When you change a part of a style, all cells to which that style has been applied change their formatting accordingly.

Use of styles takes some getting accustomed to, but may bring you great advantage. Imagine showing your nicely formatted sheet to your boss. Then your boss asks you if you could please change all input cells to having a light-yellow background fill, instead of a dark yellow one. For a large model, this may imply a huge amount of work. Would you have used styles, then it would have been a matter of seconds.

Styles are in fact an addition. Cell formatting is the sum of the applied style and all modifications to individual formatting elements on top of that style. What parts of the formatting options are included in a style is determined during the definition of the style (See screenshot below).

You access the style dialog from the Home Tab, Styles group, Cell Styles button:

Styles in the Excel 2010 ribbon
Excel enables you to access the styles by clicking the dropdown next to the styles gallery. The screen to create a new style looks like this (if you click the New Cell Style option in the style gallery):

Styles dialog of Excel

When you apply a style to a cell followed by another style, the end result will be an addition of the selected parts of both styles. What the end result of such an addition of styles will be, depends on which elements of both styles have been selected as being part of the style (this will be discussed later). Theoretically, this would have enabled us to use cascading styles, but unfortunately Excel does not keep a record of the order of applied styles. Only the last style is remembered. Also, styles can not be derived from other styles whilst maintaining a link to the parent style. Changes to the "original" style are not reflected in the "child" styles.

Creating styles

A convenient method to create a new style is by selecting a cell which has all formatting options in place which you want to incorporate in the new style. Select the cell and click the Home tab, click the New Cell Style button at the bottom of the Styles gallery.

Styles dialog

The Styles dialog screen.

To create a new style, simply type its name in the box "Style name". By default, all formatting elements are checked. Remove the checkmarks for the formatting elements you want to omit from the style you are creating (The dialog shown above has the Number and Alignment elements turned off).

Use the "Modify..." button to adjust the elements to your needs. Excel will show the standard "Format cells " dialog screen:

Format cells dialog screen

The format cells dialog screen, as shown after clicking Modify... on the Style dialog.

Note, that the elements in the Style dialog are identical to the tabs on the Format Cells dialog.

Note: As soon as you change a formatting element on a tab that was not selected on the Style dialog, Excel will automatically check that element for you; it will become part of that style.

Note that the style dialog will update/add the style, but the style will not be applied to the selected cells.

Applying styles

To apply a style to a cell, you simply click the Home tab and in the Styles group you Expand the Cell Styles gallery and click a style.

Deviate from a style

If you have applied a style to a set of cells and you change a formatting element of one of those cells, then modifications to that particular element of the style will no longer be applied to the modified cell.

So after changing a font attribute (like Bold) of a cell, changing the font attributes of the style will update all cells, except the one you just modified:

Series of cells with one style, 1 cell deviates from that style.

Series of cells with one style, 1 cell deviates from that style.

You can restore the style of a cell simply by selecting the cell and choosing the style from the style gallery.

Tips for using styles

Managing styles

If you like to keep an overview of what styles are available in your file I'd advise you to add a special worksheet to your workbook. Put the names of the styles in column A and an example output in column B:

Table with styles in a worksheet

Table with styles in a worksheet

If you need to adjust a style, select the cell in column B and adjust the style settings from there.

Creating a new style based on an existing one is easy now: Just copy the applicable row and insert it anywhere in the table. Select the cell in column B of the newly inserted row and choose Home, Cell styles gallery, New Cell Style. Enter the name of the new style and click the Format button to change the style details. Don't forget to update the Style name in column A too.

Using styles

I advise you to use styles as strictly as you can. Avoid modifying one formatting element of a cell with a style. Instead, consider if it is worth the effort to add a new style. If for instance you have a style for percentage with 2 decimal places and you have a cell which requires three, then add a style for that purpose. You can thank me later.

Adapting this method will likely trigger you to think about what cell styles your document will need. By doing this your Excel models will gradually improve. You'll gain in consistency and loose the ad-hoc (often messy) formatting jungle.

Use functional sets of styles

By looking at your Excel model you will likely be able to categorise your workbook cells into various categories:

  • Input cells
    Cells that are the main input to your model
  • Parameter cells
    Cells that contain constants for your model, such as boundaries.
  • Output cells
    Cells in an area that is meant for output, such as printing or presenting the results of a calculation on screen.
  • Calculation cells
    The cells where the actual calculation work is performed
  • Boundary cells
    By shading otherwise empty cells you can easily make areas with differing functions stand out from other areas.

Consider creating styles for each of these cell functions, each (e.g.) having its own fill color. Don't forget to make decisions on whether or not a style's locked property needs to be on or off. If you use a system like this, it becomes very easy for you to maintain your file. Imagine how easy it now becomes to change a cell from an input to an output cell: you change its style. Done.

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 may 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

International issues

The VBA Style object offers both a Name and a NameLocal property. Which might give you the impression that if your Excel is set to Dutch, you can apply the Heading 1 style to a cell using this syntax:

    ActiveCell.Style = "Heading 1"

Or perhaps like this:

    ActiveCell.Style = ActiveWorkbook.Styles("Heading 1")    

But if my Excel is set to Dutch, trying to run that code yields this error:

Error 450; "Onjuist aantal argumenten of ongeldige eigenschappentoewijzing"

This is because the Heading 1 style's NameLocal property is the same as its Name property: Kop 1

Even worse, if you run through the Styles collection, the index of the Kop 1 style differs from the index of the Heading 1 style in an English Excel. They are sorted by their localized names, in alphabetical order. Moreover, if you haven't clicked any hyperlinks yet in your current Excel session, the Hyperlink and Followed Hyperlink styles are absent from the Styles collection altogether.

This is what the English and Dutch lists look like:

Table with styles in a worksheet

Notice how the Heading styles misalign with the Kop styles.

For workbooks (and add-ins) which need to work in several different language versions of Excel, my work-around for this issue is to have some cells in a (hidden) sheet somewhere (or inside the add-in), which have been formatted as Heading 1 to 4. The add-in then reads those cell styles to get the localized style name.


There is a lot to gain by using styles in your Excel work. To name but a few:

  • Consistent formatting of your models
  • Ease of maintenance
  • A strict use of styles leads to a structured way of working
  • Less problems with your file (There is a limit on how many different cell formats Excel can handle).

With this article I have tried to give insight in the use of styles in Excel. If you have comments, suggestions or questions, please don't hesitate to use the comment form below each page!

Download a pdf version of this article




All comments about this page:

Comment by: Mike Woodhouse (5-2-2009 10:12:56) deeplink to this comment


This is another superb Office feature that Microsoft failed adequately to publicise. I only discovered it myself when I was complaining that Excel should have something like Word's styles. Then I found that it did.

I guess this is one of those things that the Ribbon was supposed to help users discover. I wonder if it works?

Comment by: Ananda Sim (5-2-2009 15:53:39) deeplink to this comment

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: Kanwaljit (6-2-2009 02:46:37) deeplink to this comment

Hi Jan,

Thanks for the great article.How can we take a print out of this ?


Comment by: Primo (6-2-2009 03:05:57) deeplink to this comment

Thanks for this.

If you have a large file and changed from formatting cells individually to using a few styles, could that reduce the size of the file slightly? Would it save any memory?

Comment by: Jan Karel Pieterse (6-2-2009 04:02:27) deeplink to this comment

Hi Primo,

Yes, it could reduce file size significantly, depending on how complex your formatting was and whether you have sheets where control+end takes you way past the really last used cell or not.

Comment by: Jan Karel Pieterse (6-2-2009 04:03:16) deeplink to this comment

Hi Kanwaljit,

I may create a pdf version of this in future, but for now there is no time. Good idea though.


Jan Karel

Comment by: Primo (6-2-2009 05:45:59) deeplink to this comment

Hi Jan Karel

That's great, this fits right in with my ongoing streamlining and standardising and general tightening up of my Excel projects.

Thanks again, and for all the other help I've had from your site over the last couple of years!

Comment by: Alan Hutchins (6-2-2009 06:16:48) deeplink to this comment

I added a Style in the way that you suggested (Excel 2003), and then added the control to one of the toolbars. The newly created style is available for selection in the workbook from which it is created, but it is NOT available in any other workbook. Have I misunderstood something?

I was hoping that I could use this to create a few specific styles that would be available for me to select in any workbook.

Comment by: AlexJ (6-2-2009 06:24:31) deeplink to this comment

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) deeplink to this comment

Hi Alex,

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

Comment by: Jan Karel Pieterse (6-2-2009 06:59:33) deeplink to this comment

Hi Alan,

You have uncovered a small omission in the article. Styles are part of a workbook, not stored anywhere else.
So if you create a style, it stays with the workbook.

You can however copy a style from one workbook to another by simply copying a cell with that style to the other file.

Also, there is a Merge button on the style dialog that lets you merge the styles from another workbook into the one you have in front of you.

Comment by: Jan Karel Pieterse (9-2-2009 11:29:04) deeplink to this comment

Hi Kanwaljit,

The last page of the article has a download link.

Comment by: Bob Phillips (19-2-2009 17:20:30) deeplink to this comment

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: Bob Phillips (19-2-2009 17:23:13) deeplink to this comment

Great idea for my style manager, build that worksheet!

Comment by: Jan Karel Pieterse (20-2-2009 05:08:18) deeplink to this comment

Hi Bob,

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

Comment by: John Pittman (3-6-2009 16:38:33) deeplink to this comment

Maybe I missed something but at the heading of this page you mention that "There are two methods to apply a style to a cell"
I would like to dynamically add "Good", Neutral", or "Bad" Styles to cells in a spreadsheet.
I can manage getting the content in cells to change, but would like to change the style as well.
Am I right in suggesting that this is not the second method?

Comment by: Jan Karel Pieterse (3-6-2009 23:40:59) deeplink to this comment

Hi John,

The text on this page isn't very clear on what the second method is. In fact I meant to write that the second method is by using the styles dropdown, which you can add to a toolbar.
I'm not sure if you can use conditional formatting to change the style in Excel 2007. If not, then I would just use a conditional format for your three situations and manually set up their formatting.

Comment by: jacky (29-8-2009 00:22:26) deeplink to this comment


Comment by: Chris Ainley (23-10-2009 18:22:08) deeplink to this comment

Just above, you mentioned that things within the Cell Styles dropdown can be added to a toolbar. I assume that there's a way of making the 'good', 'bad' icons available on a toolbar, making them more accessible for interactive tagging of cells.

How does one get the Good, Bad, Neutral, etc designation icons onto the toolbar (so that I don't have to keep using the dropdown)?



Comment by: Jan Karel Pieterse (24-10-2009 01:08:30) deeplink to this comment

Hi Chris,

I'm afraid you can't add a specific style button to the QAT. But you can create small macros and add macro buttons to the QAT for this purpose.

For example:

Sub AddBadStyle()
Selection.Style = "Bad"
ENd Sub

Comment by: Mark Allen (19-11-2009 20:00:40) deeplink to this comment

When using linear programming how can you shade in the feasible region that conatains a diagonal line through certain cells.

Thank You
Mark Allen

Comment by: Jan Karel Pieterse (20-11-2009 04:08:21) deeplink to this comment

Hi Mark,

Do you mean you want a diagonal line to appear based on some condition?

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

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) deeplink to this comment

Hi Jörgen,

Thanks for the code!

Comment by: Mahmood Anwar (7-3-2010 21:08:30) deeplink to this comment

I had 88 data and I applied style # 11. After that I added more clients and data. Now I want to combine both so that I can sort them. It does not allow me to delete the first syle and restyle or merge the top one to the bottom no style data. Thanks,

Comment by: Jan Karel Pieterse (7-3-2010 23:20:27) deeplink to this comment

Hi Mahmood,

Are you sure the new lines have become part of your table?
You can try by converting the table to a range and then converting the range back to a table.

Comment by: Elardus Mare (16-3-2010 20:42:09) deeplink to this comment

I agree on the use of styles instead of individual formatting of cells. I've alwasy used it to great effect.
One thing I could never underastand is why MS do not allow for multiple deletion of styles at once. I have several spreadsheets with data sourced from many other files. Unfortunately every time data is copied into these summary files, it also means that the summary files sometimes end up with zillions of sometimes very unlogical styles that just clutter up the styles box or even worse, their stupid form atting get5s imposed on other wheer I dont want it. Deletion of those unwanted styles certainly solve the problems, but it is very time-consuming deleting them one-by-one.

Do you have a tip for getting rid of many styles at once so I can only keep the ones I want?

I have to spend

Comment by: Jan Karel Pieterse (16-3-2010 23:26:09) deeplink to this comment

Hi Elardus,

One way is to round-trip the file through html (that is: save as filetype html and then open the html file and save as normal Excel file). That rids the file of unused styles.

If you have Excel 2007, saving to the new Excel 2007 xlsx or xlsm format does so too (the html route is less complete there, you might loose some stuff).

Comment by: Jamie (20-3-2010 08:53:48) deeplink to this comment

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) deeplink to this comment

Hi Jamie,


Comment by: tom (26-3-2010 20:26:12) deeplink to this comment

just wondering if you know the maximum number of formats in Excel 2003 or at what level to expect problems without using styles?

Comment by: Jan Karel Pieterse (27-3-2010 11:06:57) deeplink to this comment

Hi Tom,

If I recall correctly the limit is 4000. But you may start experiencing problems at lesser than that, I've heard of as little as 2500.

Comment by: Cammy (8-4-2010 08:57:15) deeplink to this comment

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) deeplink to this comment

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) deeplink to this comment

Thanks, this is just what I needed.

Comment by: Laura Hughes (19-5-2010 06:26:08) deeplink to this comment

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) deeplink to this comment

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) deeplink to this comment

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) deeplink to this comment

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) deeplink to this comment

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) deeplink to this comment

Hi Flora,

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

Comment by: arun (14-7-2010 16:10:26) deeplink to this comment

I am working styles in excel.Some styles are getting added to the work book like 20%-Accent1,20%-Accent2.

I never added these styles in my work book.Is there any way we can check unused styles.So i can remove the styles.

Comment by: Jan Karel Pieterse (15-7-2010 04:56:47) deeplink to this comment

Hi Uran,

Those Accent styles are built-in styles as of Excel 2007, My guess is you cannot remove them.

Comment by: Richard (3-10-2010 23:55:11) deeplink to this comment

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) deeplink to this comment

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) deeplink to this comment

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) deeplink to this comment

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) deeplink to this comment

Hey thanks Jan!! Will try it out.

Comment by: Siddhant Chothe (30-4-2011 01:14:14) deeplink to this comment

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) deeplink to this comment

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) deeplink to this comment

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) deeplink to this comment

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) deeplink to this comment

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) deeplink to this comment

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) deeplink to this comment

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) deeplink to this comment

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) deeplink to this comment

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

Comment by: Ivy Wong (12-10-2012 14:34:49) deeplink to this comment

I would like to perform "format as table" in Excel 2003. Someone told me that i need to do it in the Style Group. However, I can't find that helpful? Would you please kindly help?

Comment by: Jan Karel Pieterse (12-10-2012 15:10:44) deeplink to this comment

Hi Ivy,

Like this article states, this option was made available in Excel 2007. However, Excel 2003 has the option unde rthe Data menu: Data, List, Create List.

Comment by: PROF. GOVARDHAN (7-12-2012 03:43:02) deeplink to this comment

It is not wise to use one method of addition of styles to quick access in MS Word some other method in EXCEL; it makes the application so much UNFRIENDLY to the users.

Comment by: Gabriel (11-2-2013 23:31:33) deeplink to this comment

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) deeplink to this comment

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: Jan Woellhaf (7-3-2013 01:28:03) deeplink to this comment

Re: Deviate from a style.

I spent several hours trying to discover why some cells changed when I changed their style and others didn't. Wish I'd encountered your comment sooner! Excellent site. Thanks.

Comment by: Tiaan Dreyer (15-3-2013 00:34:36) deeplink to this comment

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) deeplink to this comment

Hi Tiaan,

This is one way:

Comment by: Ashwini (26-6-2013 13:46:00) deeplink to this comment

How can i see hidden styles again in Excel 2010.

Comment by: Jan Karel Pieterse (26-6-2013 15:52:36) deeplink to this comment

Hi Ashwini,

As far as I know, styles cannot be hidden so I'm afraid they are simply missing from the file in question.

If you copy cells from another excel file which does contain those styles into your file, the styles are copied as well.


- Open both files
- On the file with the styles, create a couple of cells that are formatted using the styles you need.
- Copy those cells and paste them into the file which does not have the styles.

Comment by: Joanne McClelland (12-7-2013 19:13:16) deeplink to this comment

Is there a way to search for a particular cell style? Thank you.

Comment by: Jan Karel Pieterse (13-7-2013 19:12:49) deeplink to this comment

Hi Joanne,

You can do a find and select a format to look for that matches the style in question perhaps?

Comment by: Doug (14-8-2013 22:43:19) deeplink to this comment

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) deeplink to this comment

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) deeplink to this comment

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) deeplink to this comment

Hi Doug,

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

Comment by: Jason (18-10-2013 17:44:19) deeplink to this comment

Thanks for the explanation, I think I get it - BUT
how do I change a style so that every cell where that style applies is changed. For example, I have a sheet with lots of currency cells formatted with a style to show as US Dollars but now I want all those, throughout the spreadsheet to show as GB Pounds, how do I do that please?



Comment by: Jan Karel Pieterse (19-10-2013 16:20:52) deeplink to this comment

Hi Jason,

You can right-click the syle in question and select Modify. Then change its number formatting to show GBP instead of USD.

Comment by: Jason (21-10-2013 09:03:52) deeplink to this comment

Thanks - I was trying this but I think the base spreadsheet I'm working with has some gaps in the use of styles - I'll clean it up first and ensure the cells I want to change are indeed covered by the styles.

Comment by: Sam (17-1-2014 00:54:50) deeplink to this comment

Is there a way to delete style formats quickly or in a batch? This is for Excel 2010.

Comment by: Jan Karel Pieterse (17-1-2014 11:06:03) deeplink to this comment

Hi Sam,

This little macro removes all custom styles:

Note that it may wreck your workbook's formatting, so make sure you have a backup copy!

Sub RemoveCustomStyles()
    Dim lCt As Long
    'count backwards to avoid VBA getting confused due to the delete
    For lCt = ActiveWorkbook.Styles.Count To 1 Step -1
        If ActiveWorkbook.Styles(lCt).BuiltIn = False Then
        End If
End Sub

Comment by: Doug (2-2-2014 16:00:01) deeplink to this comment

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) deeplink to this comment

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: Delise Matheny (14-2-2014 21:48:42) deeplink to this comment

Is there a way to delete style formats quickly or in a batch? I see there was a response for a macro to remove all custom styles, but I just need to delete some of the custom files (which there are several to delete). Right clicking and deleting them one at a time takes forever. Is there any other way to delete a group of styles at a time and delete?

Comment by: Jan Karel Pieterse (17-2-2014 06:23:39) deeplink to this comment

Hi Delise,

Check out this blog post:

Comment by: vickie (5-3-2014 16:36:43) deeplink to this comment

on my screen all the styles are blocked out and I can not modity any of those?

Comment by: Jan Karel Pieterse (6-3-2014 17:24:18) deeplink to this comment

Hi Vickie,

Perhaps the workbook is protected? Also, if any worksheet is protected, editing styles is not allowed.

Comment by: Rohit (6-3-2014 20:49:19) deeplink to this comment


I am running a Macro to split a file with 57 tabs to 57 separate files but but the Macro is ran and the files are created, the col. colors (Style) in the new files changes from grey to black. Any help?


Comment by: Melissa (14-3-2014 17:50:09) deeplink to this comment

How can I restore styles that I have accidentally deleted?

Comment by: Jan Karel Pieterse (15-3-2014 17:44:59) deeplink to this comment

Hi Melissa,

I'm afraid you can't, you'll have to turn to a previous copy of the file.

Comment by: Rudra (18-4-2014 10:25:29) deeplink to this comment

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) deeplink to this comment


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) deeplink to this comment

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: Patti Thomas (6-5-2014 17:45:27) deeplink to this comment

My cell styles have been "rearranged". They are in a different order and in different groups than they were before. Two of the preset styles have moved into the custom group, and one of my custom styles has moved into Good, Bad & Neutral.

Is there a way to prevent this from happening?

Is there a way to manually rearrange the styles? I'd like to put them back where they belong.

Comment by: Jan Karel Pieterse (8-5-2014 07:19:09) deeplink to this comment

Hi Patty,

Hmm, they shouldn't move at all, you have no control over their position. Are you sure the ones appearing in Custom are not really customn ones? If you right-click one of them and select Modify, what is the exact name in the top box of the dialog?

Comment by: Keith Howard (19-11-2014 21:49:19) deeplink to this comment

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) deeplink to this comment

Hi Keith,

Can you share a bit of the code please?

Comment by: Keith Howard (22-11-2014 15:43:31) deeplink to this comment

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: jenny (28-11-2014 02:52:58) deeplink to this comment

In excel what is a style sheet ?

Comment by: Jan Karel Pieterse (28-11-2014 13:25:22) deeplink to this comment

Hi Jenny,

There is no such thing per se, it is just a normal worksheet that I often insert that contains a list of styles and shows an example of how they look when applied. That is all.

Comment by: Charlie (10-12-2014 04:15:37) deeplink to this comment


The default cell styles have all converted to 20% styles, ie good, bad, normal etc are gone and I just have a list of pastel coloured cells with "20% accent" as the names. If I cut an paste any of these cells into a different book, the new book converts all the normal styles to this 20% accent. How do I dswitch it back to the normal default cell styles?



Comment by: Jan Karel Pieterse (10-12-2014 09:21:54) deeplink to this comment

Hi Charlie,

I think the only way is to copy and then pastespecial formulas, do NOT paste any formatting.

Comment by: Annie (12-1-2015 17:54:58) deeplink to this comment

On the same type of issue that Charlie mentioned - my workbooks are creating HUNDREDS of new styles of formatting that I'm not creating myself. These are huge workbooks used as a carry forward each month for tax return data - how can I keep it from creating these new styles and how can I delete them from an existing file without having to go in and delete one by has created a problem that is corrupting my files beyond repair and I'm losing material data :(

Any help would be greatly appreciated :)


Comment by: Jan Karel Pieterse (13-1-2015 10:12:06) deeplink to this comment

Hi Annie,

Styles are created mostly when you copy data from one workbook and just paste it into another including formatting. So avoiding the buildup of styles is relatively straightforward: do not include formatting when pasting but use "Values and number formats", or "Formulas and Number formats".

Comment by: Val Gaskill (17-3-2015 17:11:06) deeplink to this comment

At one point, my cell styles showed up in the styles group without having to click the drop-down to get them. Then they stopped. I'm using 2013. Can I change a setting, so a group of styles is always showing on the ribbon?

Comment by: Jan Karel Pieterse (17-3-2015 18:17:12) deeplink to this comment

Hi Val,

I'm afraid there is not much you can do to affect what styles are directly in view on the ribbon.

Comment by: BETH (20-3-2015 19:21:10) deeplink to this comment

I had the same problem and the way I restored styles is to copy workbook into new workbook and paste as xml spreadsheet to keep format of original.

Comment by: Raghavi (21-6-2015 10:22:10) deeplink to this comment

How can I style my galleries step by step?????

Comment by: Brent Dixon (6-5-2016 22:21:47) deeplink to this comment


I want to use a format style as part of a pick list. The standard behavior of a pick list is to only bring in the value, not the formatting. I can use the conditional formatting rules to very tediously tell it which format to use with each individual value on each pick list, but I'm hoping there is a way using named styles or just using the formats of the cells in the pick list.

I found the same question on another forum and the only answers offered were different forms of the tedious manual approach above - but the question had been accessed 124,000 times so apparently I'm not the only one looking for an easier answer.


Comment by: Jan Karel Pieterse (11-5-2016 16:01:48) deeplink to this comment

Hi Brent,

You could create styles for each item in the pick list which have the same names as the items. Then use a little bit of code to update the style. This goes in the code module of the worksheet in question. Assuming we only want the behaviour in cells A1:A5.

Right-click the sheet-tab and choose View code. Then paste this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1:A5")) Is Nothing Then Exit Sub
    'in case the style does not exist
    On Error Resume Next
    Target.Style = Target.Value
End Sub

Comment by: Partha (28-9-2016 06:21:01) deeplink to this comment

it really helped me a lot . thnks pal.

Comment by: Gunita (14-3-2017 16:05:04) deeplink to this comment

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) deeplink to this comment

Hi Gunita,

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

Comment by: Ren Leng Ren (23-6-2017 03:43:54) deeplink to this comment

Good for me

Comment by: Kevin W. (13-7-2017 05:02:51) deeplink to this comment

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) deeplink to this comment

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) deeplink to this comment


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) deeplink to this comment

Hi, Jan,

I solve my problem, thanks.

Comment by: Skumar (4-7-2018 11:01:33) deeplink to this comment

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) deeplink to this comment

Hi Skumar,

I'm afraid you cannot do that.

Comment by: Andy Smith (22-8-2018 21:44:51) deeplink to this comment

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) deeplink to this comment

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) deeplink to this comment

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


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

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

Hi Thaddeus,


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:

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