Modify existing custom cell format (Excel 2000)

[mike hutchins]

I have a worksheet that includes a number of custom formats that are
applied throughout to various cells. Is it possible to change an
existing custom format so that all those cells that use it are
automatically changed. For example, one of these formats is of type
0.00 "Deg" and I want to change it so that cells using this format
show values with the degree symbol "º" instead.

Since Excel doesn't seem to have a "find and replace" format feature,
how can I make such a global change without individually selecting
each applicable cell and applying a new format?

Thanks for your help....Mike

 

[jaf]

Hi Mike,
For a few cells you can click on each while holding the control key and then
change the format with the menus.
The "°" is <alt>0176 and the and you can do that in the format box.

If you have many cells you can "read" the cell format of each cell in a
range and change it with a VBA procedure.


Sub ReFormatCells()
Dim c As Object
Set RangeToFormat = Sheets("sheet2").Range("A1:G20") 'change as needed
For Each c In RangeToFormat
If c.NumberFormat = "0.00 " & Chr(34) & "Deg" & Chr(34) Then
c.NumberFormat = "0.00°"
'Debug.Print c.NumberFormat
Next c
End Sub

If your format is a lot more complicated then posted, you can use the debug
statement to help sort it out.

 

[mike hutchins]

Thanks for your advice, John. With the addition of an "End if" it
worked fine for me.
Cheers...

 

[jaf]

Good.
Actually it was a one line if. It wrapped to the next line when posted.