Find and Replace a Style

Can you find and replace a style in Excel?

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.

"Jim Bennett" wrote:

> Can you find and replace a style in Excel?

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.

"Peter Ellis" wrote:

> 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.
>
> "Jim Bennett" wrote:
>
> > Can you find and replace a style in Excel?

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

Jim Bennett wrote:
>
> 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.
>
> "Peter Ellis" wrote:
>
> > 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.
> >
> > "Jim Bennett" wrote:
> >
> > > Can you find and replace a style in Excel?

--

Dave Peterson

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

"Dave Peterson" wrote:

> 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).
>
> Jim Bennett wrote:
> >
> > 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.
> >
> > "Peter Ellis" wrote:
> >
> > > 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.
> > >
> > > "Jim Bennett" wrote:
> > >
> > > > Can you find and replace a style in Excel?
>
> --
>
> Dave Peterson
>

Hi, Just adding to that question, is there a way to search for the cell(s) that are using a particular styles. Thanks. -Keith