Subscribe in a readerStyles 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:
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":
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 loose all formatting in your file!!!
'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.
'-------------------------------------------------------------------------
' 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
Comments
Showing last 8 comments of 18 in total (Show All Comments):Comment by: Jan Karel Pieterse (4/8/2010 11:51:06 AM)Hi Cammy,
The proper syntax would be:
.Bold = True
.Color = vbRed
End With
Comment by: Cammy (4/9/2010 6:08:28 AM)Thanks, this is just what I needed.
Comment by: Laura Hughes (5/19/2010 6:26:08 AM)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 (5/19/2010 7:14:16 AM)Hi Laura,
The code works on my Excel 2010.
What error do you get exactly?
Comment by: Laura Hughes (5/19/2010 7:28:59 AM)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 (5/19/2010 11:12:41 PM)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 (5/20/2010 2:16:06 AM)hi,
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 data....am so so so disppointment because i use this sheet for presentation.
kindly help!
Comment by: Jan Karel Pieterse (5/20/2010 4:36:08 AM)Hi Flora,
No promises, but you can try sending the file to me so I can take a look.
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.