Find and Replace a Style

P

Peter Ellis

When you modify a style, Excel automatically applies the changes to all
styles in the workbook. You can copy the style if you want to save the
original.
 
J

Jim Bennett

Thanks for your post!
My problem is that I already have 2 styles in use in the spreadsheet and I
only want to use one of them. I have many cells using both styles and I want
them all to use just one. I am familiar with the procedure in Word but I
can't seem to find the same functionality in Excel.
 
D

Dave Peterson

You could use a macro to loop through all the cells--or just the cells you're
using:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range

With ActiveSheet
Set myRng = .UsedRange
For Each myCell In myRng.Cells
If myCell.Style = "BadStyle" Then
myCell.Style = "GoodStyle"
End If
Next myCell
End With
End Sub

Replace badstyle and goodstyle with the styles you want.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Be aware that this code only looks at the used range--so if you applied a style
to a whole column, this macro would only do the change in the used range area
(between the top left cell of the worksheet (usually A1, but not always) and the
cell you get when you do ctrl-end).
 
J

Jim Bennett

That is great Dave. I used it and it worked perfectly.
You also enlightened my on the ActiveSheet.UsedRange property!!
 
Top